FuelPHP Forums

remi

Hello,

I would like to make a calculation in a select in order to order by the result.

I try to do something like this :

$products = self::query();

$products->select('*', 'available_stock > minimum_stock AS is_stock');

$products->order_by('is_stock DESC')

In this exemple, I want to order my products, first the product in stock and then those out of stock.

The error displayed is : "Column not found: 1054 Champ 't0.available_stock > minimum_stock AS is_stock ' inconnu dans field list with query:"

I can't do that in PHP, so how can i do this calculation with my query ?

Thanks

Harro

You have to make it a DB::expr(), something like:

$products = static::query()
->select('*')
->select(array(DB::expr('available_stock > minimum_stock'), 'is_stock))

->order_by('is_stock DESC');

remi

Thanks.

The select is OK. I can get the result of 'available_stock > minimum_stock'.But when I do the :

->order_by('is_stock DESC');

it returns :

Column not found: 1054 Champ 't0.is_stock' inconnu dans order clause with query

Harro

Ah, wait, this is an ORM query, not a DB query? Not sure if that supports manually given aliases.

Can you dump the entire query, or check with the profiler, to see if the alias is present?

remi

yes alias is present :

SELECT available_stock > minimum_stock AS `t0_c0`,

....

ORDER BY `t10`.`name` ASC, `t1`.`name` ASC, `t0`.`is_stock` DESC

Harro

Thought so, you can't define the alias on ORM queries.

It's probably the best to see if the result ends up in the final objects, and if so, with what name, and use that to order.

The ORM isn't really designed for stuff like this.

remi

OK. Strange ...

i put on my code :

$products->order_by(\DB::expr('t0_c0'), 'DESC');

and it works. But i think is not very smart ...

In fact, i don't understand when I must use ORM query or DB query.

With the ORM query, it's linked with a model. But we can't do specific queries ?

Harro

Correct, an ORM is not a query builder, it's a record to object modeler.

You can run queries, but one of the things that is not supported is "inventing" new columns, which will cause issues with the data mapping, it is not implemented (altough as you can see technically it can work).

Harro

Remi,

https://github.com/fuel/orm/commit/c25c6e6476ffe7974e9f12b91bd7f1bf114d0fea

$products = static::query()
->select('*')
->select(array(DB::expr('available_stock > minimum_stock'), 'is_stock'))

->order_by('is_stock', 'DESC');

should now work on the latest 1.9-dev codebase.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion