My understanding is that by default ORM relationships are a left join by default, however this is not my experience when I specify a 'where' in the related model.
You have to be careful with ORM and related queries, because they are generated not using a plain join, but using a sub-query.
This is done because ORM caches results, so it needs to make sure no partial results are cached.
You can enable the profiler, and enable database profiling in your db.php, or alternatively use DB::last_query() directly after the ORM query to see what SQL has been generated by the ORM. That might shed some light on why you get this result.
It is indeed a left join in the SQL. To be honest I don't know the intricacies of SQL joins so I'm not sure if the where clause is applying to the original query or what. In my opinion this is simply ORM outputting a flawed query.
As there is no point for me to have to load every single tip from the begining of time, my workaround will be to make a key in both tables that contains the date and employee id.
But if anyone sees this and knows what's wrong please let me know. Thanks
The fact it doesn't do what you think it must do doesn't make it a flawed query.
The ORM simply is NOT a query builder that you can use to create any query you want, simply because it uses some familiar keywords. There are specific design reasons why it generates queries like that.
If you want to run a custom query that does exactly what you want, construct it using the the query builder ( DB::select()-> ...), and use as_object('Model_Mymodel') to have the result returned as ORM model objects.
I don't deem myself worthy of the insult that I don't know what the ORM is for. If my needs were more complex I wouldn't use the ORM, but I designed what I have based on what the documentation says is possible. I don't think it's crazy to just want that to work.
If this is by design then there's no reason to say "By default the Orm will join relations using a 'left' join." because while it technically is, the results are, in effect, a right join due to SQL.
Donate
