Guys have working SQL:

Symfony 1.4:

$query = Doctrine_Query::create() ->from('TechAnalys ta') ->leftJoin('ta.Translation tatr') ->leftJoin('ta.TCInstrument tci') ->leftJoin('tci.primarygroup cat') ->andWhere('ta.isActive=?', 1) ->andWhereIn('tatr.lang', $this->trans_langs) ->andWhere('tatr.isTActive =?', 1) ->orderBy('ta.created_at DESC'); private function getPager($query, $page) { $pager = new sfDoctrinePager('TechAnalys', 10); $pager->setQuery($query); $pager->setPage($page); $pager->init(); return $pager; }

Resultat:

 SELECT * FROM techanalys t LEFT JOIN techanalys_translation t2 ON t.id = t2.id LEFT JOIN t_c_instrument t3 ON t.t_c_instrument_id = t3.id LEFT JOIN trading_condition_groups t4 ON t3.primarygroupid = t4.id WHERE ( t.isactive = 1 AND t2.lang IN ('en') AND t2.istactive = 1 ) ORDER BY t.created_at DESC

That is, it will be displayed similar

 id title isretro created_at updated_at 1 aaa 0 2017-01-10 2017-01-10 2 bbb 1 2017-01-09 2017-01-09 3 ccc 0 2017-01-08 2017-01-08 4 ddd 1 2017-01-07 2017-02-01 5 eee 1 2017-01-06 2017-02-10

The techanalys t table has an techanalys t column with a value of 0 or 1, and the updated_at column shows when it is created.

It is necessary to do the following: Imagine that there are 5 records. Where 3 lines need to be duplicated and added to the main result. That is, it turns out 6 lines.

From the main query, select those that isretro and add to the main result. For isretro -> updated_at , and for normal -> created_at. It turns out to duplicate this line and insert it in the appropriate place, taking their time into account. The priority is at isretro .

If in this query it would be great to be able to select separately those that isretro and combine with the main result, considering the date.

Maybe showing the result that I want to get more accurately expresses what I want:

 id title isretro created_at updated_at 5 eee 1 2017-01-06 2017-02-10 4 ddd 1 2017-01-07 2017-02-01 1 aaa 0 2017-01-10 2017-01-10 2 bbb 1 2017-01-09 2017-01-09 2 bbb 1 2017-01-09 2017-01-09 3 ccc 0 2017-01-08 2017-01-08 4 ddd 1 2017-01-07 2017-02-01 5 eee 1 2017-01-06 2017-02-10

Thank.

  • The description and example are full of contradictions. Why wasn’t id = 5 added to the resulting set? there isretro = 1 and updated_at> created_at ... why was id = 2 added? there updated_at = created_at ... - Akina
  • @Akina I corrected the question (id = 5), and id = 2 is correct, because its updated_at <than id = 1 created_at - user216109
  • its updated_at <than id = 1 created_at Probably, this phrase has some meaning ... but I cannot understand it. - Akina
  • @Akina I corrected the question, it's my fault, I incorrectly formulated. - user216109
  • With what records are selected in addition, it seems clear - those with isretro = 1. But the final record order is completely incomprehensible ... - Akina

1 answer 1

 SELECT sq.id, sq.title, sq.isretro, sq.created_at, sq.updated_at FROM ( SELECT id, title, isretro, created_at, updated_at, 1 num FROM test UNION ALL SELECT id, title, isretro, created_at, updated_at, 2 FROM test WHERE isretro=1 ) sq ORDER BY CASE num WHEN 2 THEN GREATEST(sq.updated_at, sq.created_at) ELSE sq.created_at END DESC;

The query will give an incorrect result if there are records for which updated_at <created_a. Treated acc. CASE adjustment for num = 2.

  • But he didn’t understand :) Thanks a lot - user216109