Cakephp 3 SubQuery

349 Views Asked by At

How do i write the follow SQL statement in CakePHP3 ?

select * from feeds where feed_category_id in (SELECT feed_category_id FROM user_categories where user_id=1);

i know its a subquery but i cant figure out how to write it;

ive tried;

$usercategories = $usercategorytable->find('all')
    ->select('feed_category_id')
    ->where(['user_id'=>$user_id]);

$query->where(['feed_category_id' => $usercategories]);

But i get the error;

Error: SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

SELECT Feeds.id AS `Feeds__id`, Feeds.title AS `Feeds__title`, Feeds.url AS `Feeds__url`, Feeds.type AS `Feeds__type`, Feeds.description AS `Feeds__description`, Feeds.bhasimages AS `Feeds__bhasimages`, Feeds.user_id AS `Feeds__user_id`, Feeds.feed_category_id AS `Feeds__feed_category_id`, Feeds.feed_owner_id AS `Feeds__feed_owner_id`, Feeds.enabled AS `Feeds__enabled`, Feeds.created AS `Feeds__created`, Feeds.modified AS `Feeds__modified`, Feeds.errorflag AS `Feeds__errorflag`, Feeds.country AS `Feeds__country` FROM feeds Feeds WHERE feed_category_id = (SELECT user_categories.feed_category_id AS `user_categories__feed_category_id` FROM user_categories user_categories WHERE user_categories.user_id = :c0) LIMIT 20 OFFSET 0
0

There are 0 best solutions below