SQL syntax error when attempting to filter list view - how to build WHERE clause?

Question

I built a Joomla component for a school using component-creator.com and now I am trying to modify the source files.

My current view lists off all parents and students in my database, visible to all registered users. I am trying to modify the SQL query in models/students.php to only show the students associated with the currently logged in user. I tried to do this by modifying the where clause, but I keep getting a syntax error.

Here is the original code of models/students.php, which displays all students and parents to anybody with permission to view the form.

public function getItems()
    {
        $items = parent::getItems();

        foreach ($items as $item)
        {
            if (isset($item->parent) && $item->parent != '')
            {
                if (is_object($item->parent))
                {
                    $item->parent = \Joomla\Utilities\ArrayHelper::fromObject($item->parent);
                }

                $values = (is_array($item->parent)) ? $item->parent : explode(',', $item->parent);
                $textValue = array();

                foreach ($values as $value)
                {
                    $db = JFactory::getDbo();
                    $query = $db->getQuery(true);
                    $query
                            ->select('`#__users_2679886`.`name`')
                            ->from($db->quoteName('#__users', '#__users_2679886'))
                        ->where($db->quoteName('id') . ' = ' . $db->quote($db->escape($value)));
                    $db->setQuery($query);
                    $results = $db->loadObject();

                    if ($results)
                    {
                        $textValue[] = $results->name;
                    }
                }

                $item->parent = !empty($textValue) ? implode(', ', $textValue) : $item->parent;
            }

        }

        return $items;
    }

And here is the section of the code which I modified, producing a syntax error:

            $db = JFactory::getDbo();
            $user = JFactory::getUser();
            $query = $db->getQuery(true);
            $query
                    ->select('`#__users_2679886`.`name`')
                    ->from($db->quoteName('#__users', '#__users_2679886'))
                ->where($db->quoteName('id') . ' = ' . $db->quote($db->escape($value)) AND parent.id = $user->id);
            $db->setQuery($query);
            $results = $db->loadObject();

I simply tried to modify the WHERE cause by adding AND parent.id = $user->id, but the syntax error returned is: syntax error, unexpected '=', expecting ',' or ')'

I am looking for help to modify my specific code as well as an explanation of the WHERE clause, since Joomla's chained SQL syntax is confusing for me and I will need to perform multiple WHERE operations throughout my site design. I have consulted here and here, but I can't seem to find a clear guide showing how to build and chain the WHERE statement.

Any insight is appreciated. Thank you.

Answer 1

Try changing your query to the following:

$query->select('#__users_2679886', 'name')
      ->from($db->qN('#__users', '#__users_2679886'))
      ->where($db->qN('id') . ' = ' . $db->q($value)
      ->where($db->qN('parent.id') . ' = ' . $user->id);

The problem was coming from $db->q(parent.id = $user->id).

Other that that, I've made a few tweaks to the query for you.

Just note that you don't need to do $db->quote($db->escape('XXX')), just $db->quote('XXX') is fine.