CODEWITHSUNDEEP
phpmysqlpropel
Axel
Axel

Reputation: 61

Propel subqueries in select

I need to find article from database, and also the next and previous id of the neighbours. Right now i have 3 queries that work - first two are without the find() method - i know that.

$artNext = ArticlesQuery::create()
            ->filterById(array('min' => $artId + 1))
            ->filterByActive(1)
            ->filterByType(1)
            ->select('Id')
            ->orderById(Criteria::ASC)
            ->limit(1);


$artPrev = ArticlesQuery::create()
            ->filterById(array('max' => $artId - 1))
            ->filterByActive(1)
            ->filterByType(1)
            ->select('Id')
            ->orderById(Criteria::DESC)
            ->limit(1);


$article = ArticlesQuery::create()
            ->filterByActive(1)
            ->filterById($artId)
            ->findOne();

How can i combine artPrev and artNext subqueries to be inserted as in the following query (simplified)

select id,
(select id from articles where id<77 ORDER BY id DESC limit 1 ) as prev,
(select id from articles where id>77 ORDER BY id ASC limit 1) as next
from articles 
where id=77

Upvotes: 2

Views: 2746

Answers (2)

Thomas
Thomas

Reputation: 198

Alternate approach:

If your IDs are incremented by 1 and never deleted or omitted, you could do:

$article = ArticlesQuery::create()
    ->filterByActive(1)
    ->filterById($artId)
    ->_or()
    ->filterById($artId-1)
    ->_or()
    ->filterById($artId+1)
    ->withColumn('Articles.Id','id')
    ->withColumn(('IF Articles.Id ='.$artId.',"current",(IF Articles.Id ='.$artId+1.',"next","prev"))','position')
    ->find();

That gives you an $article Object with three "rows" like this (Assumin your ArtId is 20 again)

(0) 
    id=19
    position=prev
(1) 
    id=20
    position=current
(2) 
    id=21
    position=next

As I said: Another approach completely. But you have to accept: You cannot merge three queries that aren't "logically related" into one. Even if you make it seem like it by an elegant approach in your programming language or database abstracting layer, it'll always result in three queries executed by the database engine!

Hope this helps!

Upvotes: 0

Thomas
Thomas

Reputation: 198

How about...

$artNext = ArticlesQuery::create()
        ->filterById(array('min' => $artId + 1))
        ->filterByActive(1)
        ->filterByType(1)
        ->select('Id')
        ->orderById(Criteria::ASC)
        ->limit(1);
        ->find();

$artPrev = ArticlesQuery::create()
        ->filterById(array('max' => $artId - 1))
        ->filterByActive(1)
        ->filterByType(1)
        ->select('Id')
        ->orderById(Criteria::DESC)
        ->limit(1);
        ->find();

$article = ArticlesQuery::create()
        ->filterByActive(1)
        ->filterById($artId)
        ->withColumn('Articles.Id','id')
        ->withColumn($artPrev->getId(),'prev')
        ->withColumn($artNext->getId(),'next')      
        ->findOne();

Now you can use it like so:

$nextString = $article->getNext();
$prevString = $article->getPrev();
$idString = $article->getId();

The "->withColumn" acts like a subselect in this case, just with previously determined values:

SELECT
    "19" AS prev,
    id
    "21" AS next
FROM
    articles;

Upvotes: 1

Related Questions