Order by a column from another table

[da...@dasim.cz]

Hello!

I have a following structure:

Table customer: id, name

Table additional_field: id, name, type

Table additional_data: id, additional_field_id, customer_id, value

There are multiple additional_fields any customer can have a value for. Customer might not have a particular field value set.

I would like to order customers by a value of a particular additional_field.

In pure SQL it’s simple (1000 is an id of the particular field):

SELECT customer.*, `value`

FROM customer

LEFT JOIN additional_data ON customer.id = additional_data.customer_id AND additional_field_id = 1000

ORDER BY `value` DESC;

Is there a way how to achieve this in NotORM? I tried a lot of ways but either it’s impossible or I just miss something obvious here.

Thanks for any help.

[Allysson David]

You know, having the `additional_field_id` together with the ON clause isn't really good.

Should move it to the WHERE clause.

Something like this:

$db->customer()->select('customer.*, additional_data:value')->where('additional_data.additional_field_id', 1000)->order('additional_data:value DESC');

If you don't really need the value you can remove it from the select.

[da...@dasim.cz]

Thank you, that at least helped to get a working version I couldn’t achieve myself.

The reason I put `additional_field_id` together with the ON clause is that a customer might not have a particular field value set at all.

Which means I would’t get those customers with no value for the field (additional_field_id =1000) in a result at all.

But I want them there, with a value of NULL which using `additional_field_id` together with the ON clause provides.

Do you think it’s possible to achieve it through NotORM?

Dne pátek 9. ledna 2015 13:12:57 UTC+1 Allysson David napsal(a):

[Allysson David]

Oh, yes, I totally missed that part.

Sadly notorm only allows you to have left joins and you can't specify the condition on the joining.

There used to be a code somewhere for you to customize notorm and build your own join, but it's been removed for some reason...

I suppose that if you don't wish to edit notorm you could create a view with the join and select from it...

--
You received this message because you are subscribed to the Google Groups "NotORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to notorm+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.