Conditional join on one to one relationship about nodal HOT 10 CLOSED

Hi Mike,

Sorry about the delay. One thing I see that you shouldn't be doing is joining via: id. Try writing the relationship as:

WorkOrder.joinsTo(Property, {multiple: false});

This assumes that WorkOrder has a property named property_id. Let me know if that helps! Should also remove the need to do a conditional join, your query looks like it should be OK even without the __is at the end.

Best,

Jacob

from nodal.

As an aside, you should use via for cases where you want to have different one to one relationships between two models. As an example, say you wanted WorkOrder to be associated with exactly two different Property models in semantically different ways.

For example, you're doing the join from WorkOrder to a Property via the default property_id, but say a WorkOrder could have exactly one workshop associated with it that is also a Property in your database, you could have a workshop_id property on your WorkOrder and then have a line in your relationships.js file like this:

WorkOrder.joinsTo(Property, {via: 'workshop_id', multiple: false});

from nodal.

Jacob,

When I remove the "via" I end up getting the following error: "column w1.work_order_id does not exist". In this relationship the property_id column is in the work_orders table, and I need to join that property_id column back to the id column in properties, then restrict by properties.company_id.

Assuming the relationship syntax from the docs says child.joinsTo(parent), this is the relationship I added:

Property.joinsTo(WorkOrder, {multiple: false });

I am attempting to join the table "properties" to "work_orders" so that I can restrict the work_orders based on the company_id in the parent table of properties. Essentially making it so that someone in company XYZ cannot see rows from the work_orders table that are related to a property they do not own.

Thanks for the response!

from nodal.

Ah I had it backwards then... My bad. Glad I could help!

from nodal.

Jacob,

I actually didn't resolve the issue, I am still running into the error "column w1.work_order_id does not exist" after removing the via. So the original question was how to I force the relationship to use the correct columns work_orders.property_id -> properties.id?

from nodal.

Oh did

WorkOrder.joinsTo(Property, {multiple: false});

not resolve your issue? That should do it, you can do joins in queries the other way too :)

from nodal.

The system is erroring saying "message": "column w1.work_order_id does not exist". What I believe is happening is the normal join expects the parent table to use an 'id' column, and the child table would have 'parent_table_id' column, hence it's looking for 'work_order_id' in properties because the join is making the work_orders table the parent. I need to force the relationship to use 'work_orders.property_id' as the parent column, and join on the child column of 'properties.id'.

from nodal.

If WorkOrder.joinsTo(Property, {multiple: false}); isn't working for you, you could do something like this:

Property.joinsTo(WorkOrder, {via: 'id', using: 'property_id', multiple: false});

But that's pretty irregular. The using field is meant for special cases, not joining on ids. You shouldn't have to do that, and we should investigate why WorkOrder.joinsTo(Property, {multiple: false}); isn't working for you -- could be a Nodal bug.

from nodal.

Yes! That worked! It was the 'using' key that was missing, and now the records are returning only from the properties that are related to the correct company_id. Thank you so much!

from nodal.

Happy to help!

from nodal.