Database query with OR WHERE and AND WHERE not working

Question

I'm creating a custom search form in Joomla! to search articles from certain categories.

Here is my query:

$categories = array('2', '12', '16');
$query
    ->select(array('id', 'title', 'catid'))
    ->from('#__content')
    ->where($db->quoteName('title') . ' LIKE ' . $db->quote('%' . $searchterm . '%'), 'OR')
    ->where($db->quoteName('introtext') . ' LIKE ' . $db->quote('%' . $searchterm . '%'))
    ->where($db->quoteName('catid') . ' IN (' . implode(',', $categories) . ')');

I tried it this way too:

$categories = array('2', '12', '16');
$query
    ->select(array('id', 'title', 'catid'))
    ->from('#__content')
    ->where($db->quoteName('catid') . ' IN (' . implode(',', $categories) . ')')
    ->where($db->quoteName('title') . ' LIKE ' . $db->quote('%' . $searchterm . '%'), 'OR')
    ->where($db->quoteName('introtext') . ' LIKE ' . $db->quote('%' . $searchterm . '%'));

Issue: It's showing results from all categories instead of specified categories.

Answer 1

Use extendWhere() method with AND as outer glue and OR as inner glue:

$query
    ->where($db->quoteName('catid') . ' IN (' . implode(',', $categories) . ')')
    ->extendWhere(
        'AND',
        [
            $db->quoteName('title') . ' LIKE ' . $db->quote('%' . $searchterm . '%'),
            $db->quoteName('introtext') . ' LIKE ' . $db->quote('%' . $searchterm . '%'),
        ],
        'OR'
    );

Or the shorthand andWhere() method:

$query
    ->where($db->quoteName('catid') . ' IN (' . implode(',', $categories) . ')')
    ->andWhere(
        [
            $db->quoteName('title') . ' LIKE ' . $db->quote('%' . $searchterm . '%'),
            $db->quoteName('introtext') . ' LIKE ' . $db->quote('%' . $searchterm . '%'),
        ]
    );

The resulting query:

SELECT id,title,catid
FROM #__content
WHERE 
(`catid` IN (2,12,16)) AND 
(`title` LIKE '%searchterm%' OR `introtext` LIKE '%searchterm%')

Note: to use these methods there must be at least one where() call made before.

Answer 2

Try:

$query->orWhere($db->quoteName('title') . ' LIKE ' . $db->quote('%' . $searchterm . '%'));