pdox

pdox copied to clipboard

I want sql:

SELECT articles.title,articles.id,articles.channel_id
FROM articles
INNER JOIN articles_tags ON articles.id = articles_tags.articles_id
INNER JOIN tags ON articles_tags.tags_id = tags.id
WHERE tags.id IN (
     SELECT tags.id
     FROM tags INNER JOIN articles_tags ON tags.id = articles_tags.tags_id
     WHERE tags.is_hidden = 1 AND articles_tags.articles_id = 111
     LIMIT 12
)
GROUP BY articles.id
ORDER BY articles.created_at DESC

This is what I did:

// other codes

$otherIds = $db->table('tags')->select('tags.id')
                          ->innerJoin('articles_tags', 'tags.id', 'articles_tags.tags_id')
                          ->where('tags.is_hidden', '=', '1')
                          ->where('articles_tags.articles_id', '=', 111)
                          ->limit(12)
                          ->getAll('array');

$db->table('articles')->select('articles.title,articles.id,articles.channel_id')
      ->innerJoin('articles_tags','articles.id','articles_tags.articles_id')
      ->innerJoin('tags','articles_tags.tags_id'_id','tags.id')
      ->in('tags.id',$otherIds)
      ->groupBy('articles.id')
      ->orderBy('articles.created_at', 'DESC')
      ->getAll();

// other codes

Is there any method similar to subSelect? Or any other suggestions?

$db->table('articles')->select('articles.title,articles.id,articles.channel_id')
      ->innerJoin('articles_tags','articles.id','articles_tags.articles_id')
      ->innerJoin('tags','articles_tags.tags_id'_id','tags.id')
      ->in(
           'tags.id',
           subSelect(
                    $db->table('tags')->select('tags.id')
                      ->innerJoin('articles_tags', 'tags.id', 'articles_tags.tags_id')
                      ->where('tags.is_hidden', '=', '1')
                      ->where('articles_tags.articles_id', '=', 111)
                      ->limit(12)
                      ->getAll('array')
           )
       )
      ->groupBy('articles.id')
      ->orderBy('articles.created_at', 'DESC')
      ->getAll();

Hi @lee-php , There is no support using subquery for now but I'll add it to ToDO list. Now, you can use query method and write pure SQL code in order to run your query.

Thanks.

OK, thanks izniburak. Wait for your improvement.