Propel ORM: Order By FIELD

Question

I'm trying to do a query with a custom "order by" with Propel 1.6

select * from myObject ORDER BY FIELD(id, 3, 11, 7, 1)

this does not work:

myObjectQuery::create()
    ->orderById($someIds)
    ->find()

how can i do?

You can add it yourself? https://yogeshsalvi.wordpress.com/2010/01/12/steps-to-implement-mysql-order-by-field-in-symfony-propel/ — HansP, Dec 10 '15 at 16:05
thanks for helping, but it's an old fashioned criteria-way solution, i'm looking for more generic and modern solution. — Lionel, Dec 11 '15 at 14:19

score 2 · Answer 1 · answered Jul 29 '20 at 10:32

2

$ids = array(3, 11, 7, 1);

$cids = implode(',', $ids);

myObjectQuery::create()
    ->addAscendingOrderByColumn("FIELD (tableName.id, {$cids})")
    ->find()

answered Jul 29 '20 at 10:32

Павел Дорогой

21
2

1

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run. – 7uc1f3r Jul 29 '20 at 11:52

arcee123 · Answer 2 · 2015-12-11T15:34:08.430

0

you can stack order-by's in order that you want:

myObjectQuery::create()
    ->orderByField1
    ->orderbyField3('desc')
    ->orderbyField2
    ->find()

Try that.

Update 2:

$con = Propel::getConnection();
$query = 'SELECT COUNT(t1.user) AS users, t1.choice AS lft, t2.choice AS rgt
  FROM choice t1 iNNER JOIN choice t2 ON (t1.user = t2.user)
  WHERE t1.choice IN (?, ?) AND t2.choice IN (?, ?)
  GROUP BY t1.choice, t2.choice';
$stmt = $con->prepare($query);
$stmt->bindValue(1, 'foo');
$stmt->bindValue(2, 'bar');
$stmt->bindValue(3, 'baz');
$stmt->bindValue(4, 'foz');
$res = $stmt->execute();

in your case, I would establish $con, create a query to get your value list, then use a for loop to assign your $stmt->bindValue(#,#) and then execute.

edited Dec 11 '15 at 15:34

answered Dec 10 '15 at 21:54

arcee123

101
9
41
118

1

I'm trying to make an orderBy on the same field, depending of field values: your solution is for a classical orderBy with different fields. – Lionel Dec 11 '15 at 14:20
try Update 2. Maybe that'll get you what you want. – arcee123 Dec 11 '15 at 15:37
thanks, for sure it works, it's pure SQL, but i was looking for a pure Propel way. – Lionel Dec 12 '15 at 16:09

score -2 · Answer 3 · edited Dec 01 '16 at 23:49

-2

$ids = array(3, 11, 7, 1);

$cids = implode(',', $ids);

myObjectQuery::create()
    ->orderBy("FIELD(id, {$cids})")
    ->find()

edited Dec 01 '16 at 23:49

johnnyRose

7,310
17
40
61

answered Dec 01 '16 at 14:17

kostas trichas

2,923
7
28
37

Propel ORM: Order By FIELD

3 Answers3

Linked