Propel ORM: Order By FIELD

4
votes

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?

3
propel
You can add it yourself? yogeshsalvi.wordpress.com/2010/01/12/… – HansP
thanks for helping, but it's an old fashioned criteria-way solution, i'm looking for more generic and modern solution. – Lionel

3 Answers

0
votes

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.

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

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

myObjectQuery::create()
    ->addAscendingOrderByColumn("FIELD (tableName.id, {$cids})")
    ->find()
-2
votes
$ids = array(3, 11, 7, 1);

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

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