1

I'm having an issue with Zend Framework 2's SQL class. It's saying it cannot find a certain column in the table but the column is there. I've run into this problem before and had to use actual SQL syntax instead of building the query. I rather not have to resort to putting the actual syntax in and instead figure out why this is happening so I can avoid having this issue in the future.

Here is my code:

namespace Members\Model;

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Select;
use Zend\Db\Adapter\Adapter;
use Zend\Db\Sql\Predicate;

use Members\Model\Interfaces\FeedInterface;
use Members\Model\Exceptions\FeedException;



class FeedModel implements FeedInterface
{
    /**
     * @var TableGateway
     */
    public $gateway;

    /**
     * @var string
     */
    public $user;

    /**
     * @var Sql
     */
    public $sql;

    /**
     * @var Select
     */
    public $select;


    /**
     * Constructor method for FeedModel class
     * @param TableGateway $gateway
     * @param string $user
     */
    public function __construct(TableGateway $gateway, $user)
    {
        $this->gateway = $gateway instanceof TableGateway ? $gateway : null;

        $this->select = new Select();

        $this->sql = new Sql($this->gateway->getAdapter());

        $this->user = $user;
    }


    /**
     * {@inheritDoc}
     * @see \Members\Model\Interfaces\FeedInterface::listFriendsStatus()
     */
    public function listFriendsStatus()
    {
        // get the friend ids based on user id
        // and then compare the friend id to the id in status table
        $friend_query = $this->select->columns(array('*'))
        ->from('friends')
        ->where(array('user_id' => $this->getUserId()['id']));  // the issue

        file_put_contents(getcwd() . '/public/query.txt', $this->sql->buildSqlString($friend_query));

        $query = $this->sql->getAdapter()->query(
            $this->sql->buildSqlString($friend_query),
            Adapter::QUERY_MODE_EXECUTE
        );

        if ($query->count() > 0) {
            $friend_id = array();

            foreach ($query as $result) {
                $friend_id[] = $result;
            }

            $this->select->columns(array('status'))
            ->from('status')
            ->where(array('id' => array_values($friend_id), new Predicate\IsNotNull('id')));

            $status_query = $this->sql->getAdapter()->query(
                $this->sql->buildSqlString($this->select),
                Adapter::QUERY_MODE_EXECUTE
            );

            if ($status_query->count() > 0) {
                // check if a image was used
                $this->select->columns('username')
                ->from('members')
                ->where(array('id' => array_values($friend_id), new Predicate\IsNotNull('id')));

                $image_query = $this->sql->getAdapter()->query(
                    $this->sql->buildSqlString($this->select),
                    Adapter::QUERY_MODE_EXECUTE
                );

                if ($image_query->count() > 0) {
                    $status_dir = array();

                    foreach ($image_query as $value) {
                        if (@!is_dir(getcwd() . '/public/images/profile/' . $value['username'] . '/status/')) {
                            continue; 
                        }

                        $status_dir[] = getcwd() . '/public/images/profile/' . $value['username'] . '/status/';
                    }

                    $images = array();

                    // retrieve the image inside the status directory
                    foreach (array_diff(scandir($status_dir, 1), array('.', '..')) as $values) {
                        $images[] = $values;    
                    }
                } else {
                    throw new FeedException("The user does not exist in the user table.");
                }

                $status = array();

                // get all the statuses
                foreach ($status_query as $rows) {
                    $status[] = $rows;    
                }

                return array('status' => $status, 'images' => $images);
            } else {
                throw new FeedException("No status was found for your friends.");
            }
         } else {
            throw new FeedException(sprintf("Could not locate any friends for %s", $this->user));
         }
    }


    /**
     * {@inheritDoc}
     * @see \Members\Model\Interfaces\FeedInterface::hideFriendsStatus()
     */
    public function hideFriendsStatus($friend_id)
    {

    }



    /**
     * Grabs the user id for the user
     * 
     * @return int|boolean
     */
    public function getUserId()
    {
        $this->select->columns(array('*'))
        ->from('members')
        ->where(array('username' => $this->user));

        $query = $this->sql->getAdapter()->query(
            $this->sql->buildSqlString($this->select),
            Adapter::QUERY_MODE_EXECUTE
        );

        if ($query->count() > 0) {

            foreach ($query as $result) {
                $row = $result;
            }

            return $row;
        }

        return false;
    }
}

This is the exception message I am getting:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user_id' in 'where clause' 

But as you can see in this screenshot, the column user_id exists in the friends table I have in place:

enter image description here

So my question is why is it doing this and how can I in the future avoid this issue?

Thanks!

4
  • echo $this->sql->buildSqlString($friend_query); and paste it here. Also run in the query browser and see what happens. Commented Mar 10, 2018 at 13:02
  • SELECT members.* FROM members WHERE username = 'fooboy' AND user_id = '1' now why is it putting that.. :\ Commented Mar 10, 2018 at 17:02
  • it should be (or at least I want it to) SELECT friends.* FROM friends WHERE user_id = $this->getUserId['id'] Commented Mar 10, 2018 at 17:05
  • I put the whole file's code in, maybe it will explain better. Commented Mar 10, 2018 at 18:45

1 Answer 1

1

It looks like the select is causing the issue.

Since your code ->from('friends') is called first and then it is overridden due to this function call $this->getUserId(), which overrides the friends table to members due to ->from('members').

Try changing your code to.

$userId = $this->getUserId()['id'];
$friend_query = $this->select->columns(array('*'))
        ->from('friends')
        ->where(array('user_id' => $userId));

This should work, but if it doesn't, try to just create new select object $select = new Select(); in both the functions rather than $this->select = new Select();.

Sign up to request clarification or add additional context in comments.

1 Comment

yeah i was thinking that as well after looking at it

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.