0

I have two tables , Website and user_website_map

I want to select Websites with nested select condition

Code is this

$query = \Website::query()->addWhere('id in (SELECT user_website_map.website_id from security.user_website_map where user_id = :userId )', [':userId' =>  $this->getId()]) ;
        return $query->execute();

This statements throws exception 

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token SELECT, near to ' user_website_map.website_id from security.user_website_map where user_id = :userId )', when parsing: SELECT [Website].* FROM [Website] WHERE id in (SELECT user_website_map.website_id from security.user_website_map where user_id = :userId ) (138)

If i execute this query from db console no error occures. 

SELECT  * FROM main.website WHERE  id in (SELECT user_website_map.website_id from security.user_website_map where user_id = 2)

For information I am using postgresql-9.4 and Phalcon 1.3.4 What is wrong with this statements ?

Taleh Ibrahimli
  • 750
  • 4
  • 13
  • 29

1 Answers1

1

This query could--and should--be accomplished with a join rather than a sub-query:

$query = \Website->query()
    ->join('UserWebsiteMap') // replace with actual model name
    ->where('user_id = :user_id:', array('user_id' => $this->getId()));

While I am more familiar with MySQL than with PostgreSQL, I believe it is true for both databases that sub-queries have a negative impact on performance, relative to joins. See the following link for an explanation of why: Join vs. sub-query.

Community
  • 1
  • 1
Tim Burch
  • 1,088
  • 7
  • 9