FuelPHP Forums

adamBB

I am using in CRUD the and_where_open() method as per the docs (http://fuelphp.com/docs/packages/orm/crud.html#/complex_wheres) along with a has_many related query and a limit().

I have noticed that this isnt always returning me the correct result set.

When looking at the query the 'where' clauses inside the subquery are not being set at all meaning that the whole dataset is returned while retaining the limit/offset

SELECT `t0`.`things` AS `t0_c0` FROM (SELECT `t0`.`things` FROM `table` AS `t0` WHERE `t0`.`disabled` = 0 ORDER BY `t0`.`created_at` DESC LIMIT 20 OFFSET 0) AS `t0` LEFT JOIN `table_2` AS `t1` ON (`t0`.`id` = `t1`.`id`) WHERE `title` LIKE '%my_string%' OR `t0`.`year` = '%my_string%' ORDER BY `t0`.`created_at` DESC

as you can see in the above query that CRUD makes, in the subquery it will get all the results without my where clause but still applying a limit and then it will apply the where clause outside the subquery meaning that my dataset is not complete.

Harro

This is standard behaviour of the ORM, when you add relations, as documented.

Since the ORM is not about single flattened rows, but about related objects, it always tries to keep resultsets complete, which is why the limit is in the subquery. If you allow incomplete results, you can never fetch the rest, due to the ORM cache.

If you are sure you only need row results (for example for pagination), use rows_limit() instead of limit(), same for offset.

adamBB

Thank you for the reply, but as you see this doesnt work well either for me.

Since the related has one_to_many. When setting rows_limit (and/or rows_offest) it will be applied to the whole dataset (the whole query)

Lets say our database consists of 15 blog posts and each blog post has 10 comments ($has_many relation)

if we apply the rows_limit at say 10 it mights it will return only 2 posts (rather than the desired 10) since we have 5 rows per post (5 comments)

so this is where we want to use limit() instead, right? which will generate us the subquery (this is all ok and fine) what is not OK is when I use the and_where_open() addition these statements (in my theory) should be applied to both the inner subquery and the main query, while currently they are applied ONLY the the main query and not the subquery. while the limit is applied in the subquery

this results in a non filtered subquery but with a limit set.

I hope this makes sense.

**************EDIT**************
I am mistaken, seems that any where() that uses the joined table doesnt get put into the subquery

$query->where('table2.field', 'LIKE', '%blah%');

this does not get applied to the inside of the subquery which like I said above doesn't filter my initial dataset but still sets a hard limit offset

Harro

Can you post the query you have coded, and define the exact outcome you want it to produce?

adamBB

Hi Harro and thank you for the replies so far.

my current CRUD query is as follows:

$query = Model_Movie::query()->related('actors')->related('directors');

$query->and_where_open()

->where('title', 'LIKE', '%blah%')

->or_where('year', 'blah')

->or_where('actors.actor_name', 'LIKE', '%blah%')

->or_where('directors.director_name', 'LIKE', '%blah%')

->and_where_close();

$query->where('movie_disabled', 0);

the produced query results in this:

SELECT `t0`.`downloads` AS `t0_c0`, `t0`.`rt_critics_rating` AS `t0_c1`, `t0`.`rt_critics_score` AS `t0_c2`, `t0`.`rt_audience_rating` AS `t0_c3`, `t0`.`rt_audience_score` AS `t0_c4`, `t0`.`runtime` AS `t0_c5`, `t0`.`movie_disabled` AS `t0_c6`, `t0`.`dmca_removed` AS `t0_c7`, `t0`.`id` AS `t0_c8`, `t0`.`user_id` AS `t0_c9`, `t0`.`imdb_code` AS `t0_c10`, `t0`.`title` AS `t0_c11`, `t0`.`year` AS `t0_c12`, `t0`.`rating` AS `t0_c13`, `t0`.`genre1` AS `t0_c14`, `t0`.`genre2` AS `t0_c15`, `t0`.`mpa_rating` AS `t0_c16`, `t0`.`description_intro` AS `t0_c17`, `t0`.`description_full` AS `t0_c18`, `t0`.`trailer_code` AS `t0_c19`, `t0`.`cover_image` AS `t0_c20`, `t0`.`background_image` AS `t0_c21`, `t0`.`image1` AS `t0_c22`, `t0`.`image2` AS `t0_c23`, `t0`.`image3` AS `t0_c24`, `t0`.`language` AS `t0_c25`, `t0`.`subtitle` AS `t0_c26`, `t0`.`url` AS `t0_c27`, `t0`.`uploader_notes` AS `t0_c28`, `t0`.`imdb_last_update` AS `t0_c29`, `t0`.`created_at` AS `t0_c30`, `t0`.`updated_at` AS `t0_c31`, `t1`.`image` AS `t1_c0`, `t1`.`id` AS `t1_c1`, `t1`.`movie_id` AS `t1_c2`, `t1`.`imdb_code` AS `t1_c3`, `t1`.`actor_name` AS `t1_c4`, `t1`.`character_name` AS `t1_c5`, `t1`.`created_at` AS `t1_c6`, `t1`.`updated_at` AS `t1_c7`, `t2`.`image` AS `t2_c0`, `t2`.`id` AS `t2_c1`, `t2`.`movie_id` AS `t2_c2`, `t2`.`imdb_code` AS `t2_c3`, `t2`.`director_name` AS `t2_c4`, `t2`.`created_at` AS `t2_c5`, `t2`.`updated_at` AS `t2_c6` FROM (SELECT `t0`.`downloads`, `t0`.`rt_critics_rating`, `t0`.`rt_critics_score`, `t0`.`rt_audience_rating`, `t0`.`rt_audience_score`, `t0`.`runtime`, `t0`.`movie_disabled`, `t0`.`dmca_removed`, `t0`.`id`, `t0`.`user_id`, `t0`.`imdb_code`, `t0`.`title`, `t0`.`year`, `t0`.`rating`, `t0`.`genre1`, `t0`.`genre2`, `t0`.`mpa_rating`, `t0`.`description_intro`, `t0`.`description_full`, `t0`.`trailer_code`, `t0`.`cover_image`, `t0`.`background_image`, `t0`.`image1`, `t0`.`image2`, `t0`.`image3`, `t0`.`language`, `t0`.`subtitle`, `t0`.`url`, `t0`.`uploader_notes`, `t0`.`imdb_last_update`, `t0`.`created_at`, `t0`.`updated_at` FROM `movies` AS `t0` WHERE `t0`.`movie_disabled` = 0 ORDER BY `t0`.`created_at` DESC LIMIT 20 OFFSET 0) AS `t0` LEFT JOIN `actors` AS `t1` ON (`t0`.`id` = `t1`.`movie_id`) LEFT JOIN `directors` AS `t2` ON (`t0`.`id` = `t2`.`movie_id`) WHERE `t2`.`imdb_code` LIKE '%blah%' AND (`t0`.`title` LIKE '%blah%' OR `t0`.`year` = 'blah' OR `t1`.`actor_name` LIKE '%blah%' OR `t2`.`director_name` LIKE '%blah%') ORDER BY `t0`.`created_at` DESC

and as you can see in the subquery we have this:

SELECT `t0`.`downloads`, `t0`.`rt_critics_rating`, `t0`.`rt_critics_score`, `t0`.`rt_audience_rating`, `t0`.`rt_audience_score`, `t0`.`runtime`, `t0`.`movie_disabled`, `t0`.`dmca_removed`, `t0`.`id`, `t0`.`user_id`, `t0`.`imdb_code`, `t0`.`title`, `t0`.`year`, `t0`.`rating`, `t0`.`genre1`, `t0`.`genre2`, `t0`.`mpa_rating`, `t0`.`description_intro`, `t0`.`description_full`, `t0`.`trailer_code`, `t0`.`cover_image`, `t0`.`background_image`, `t0`.`image1`, `t0`.`image2`, `t0`.`image3`, `t0`.`language`, `t0`.`subtitle`, `t0`.`url`, `t0`.`uploader_notes`, `t0`.`imdb_last_update`, `t0`.`created_at`, `t0`.`updated_at` FROM `movies` AS `t0` WHERE `t0`.`movie_disabled` = 0 ORDER BY `t0`.`created_at` DESC LIMIT 20 OFFSET 0

even though we are trying to filter out options on actors and directors and also title (as can be seen in the where_open clause) this doesnt get applied in the subquery but the thing is the limit does, so that will return only a limited dataset and then get filtered more and this means that even though we have data that matches our ORM query, it might return 0 data (all depeneds how far down our matching data is and also how big our limit is.

I hope this more sense.

I am sorry I am not sure what the expected query will be, but I would have assumed that there would have been joined the related tables in the subquery to be able to search them first BEFORE applying the limit (and/or offset) but thats just me and my inexperienced thinking

Harro

It can't move the other where's because they are encased in parentheses. And it isn't fool-proof in the sense that in this case it would have been better to have the disabled filter on the outer query too.

But if you use rows_limit(20), it will not apply the limit on the subquery, but on the outer query, producing a standard join result suitable for pagination. And then the position of the where clause should no longer matter, since the where's act on the join as a whole.

adamBB

Thank you for the reply.

Regarding to the rows_limit() yes thats what I initially thought too, but it doesnt work well. In my actual real case, each movie has multiple actors/directors
when we run the query with rows_limit() it will show each movie as 5 rows (since there are say 4 actors and 1 direcotr and if we say limit 10 then it will get only 2 movies (instead of the desired 10)

now if I knew how many directors/actors per movie then I would know to multiple the limit by the number but sadly actor/director count is not consistent.

Any ideas how to achieve my search query with limits (pagination) ?

Harro

That is how normally a limit on a join works, yes. Which for pagination would not be a problem, since that would display 10 lines, 5 for movie one, 5 for movie two.

What you want is a hierarchical model structure with a flat display, you want to return only movie info, but want to filter on related table data. which is not what the ORM was designed for.

If you want this, write a normal DB::select() with manual joins, and use ->as_object('Model_Movie') to have the result returned as ORM model objects.

adamBB

I have tried to set this and this is the first time I am trying to use a DB::select()

here is my query.

DB::select()->from('movies')->join('actors','LEFT')->on('movies.id', '=', 'actors.movie_id')->join('directors','LEFT')->on('movies.id', '=', 'directors.movie_id')->as_object('Model_Movie')->execute();

nothing fancy, just want to see the final result.

Sadly I get back this:

SQLSTATE[HY000]: General error: could not call class constructor

http://i.imgur.com/dn9fgIL.png

here is my config/db.php

return array(

'default' => array(

'type' => 'pdo',

'connection' => array(

'dsn' => 'mysql:host=localhost;dbname=db_name',

'hostname' => 'localhost',

'username' => 'root',

'password' => 'password',

'database' => 'db_name',

),

);

Sorry for the ongoing issues, and thank you for the help

Harro

Your Model_Movie is an ORM model? That extends \Orm\Model? And does not define it's own constructor?

adamBB

Here is my movie model

http://i.imgur.com/m0X3VKC.png

Would you be able to identify where I have gone wrong with this?

I am sorry if my questions are little newbie and thank you for the help thus far.

Harro

Not sure what happened there. Model looks fine.

Can you add a limit(1) to that query and try again? I've seen issues like this, but not with MySQL.

The only other thing I can think of, is that your query result returns a column name that happens to be a protected property in your model, you get this error if setting an object property fails.

adamBB

Just tried using a limit(1) and getting the exact same error.

Could be due to the sql drivers I have? I have php5-mysqlnd (not php5-mysql) does this matter?
What else could I try?

doing just a as_object(); works ok. but as_object('Model_Movie'); give the PDOException [ HY000 ]: SQLSTATE[HY000]: General error: could not call class constructor error

Harro

Then the only thing I can think of is the property name collision. I've got php-mysqlnd installed as well.

This method isn't really meant for joins (it will not do any hydration and create related objects).

Does it work if you do:

DB::select()->from('movies')->as_object('Model_Movie')->execute();

If this works, it must be a column name collision somewhere.

p.s. you are on a recent Fuel version, are you?

adamBB

Yep that certainly worked. and got the data which I needed. was this because of the joins?

Running on Fuel 1.7.1

Harro

The problem with this PDO option is that it creates the object, stores the properties, and then runs the constructor. So there is some wizardry in the model constructor to deal with this.

Secondly, any properties that are being set, that are not defined in your $_properties array, will be stored as custom properties. This will be done through the magic setter.

You get this error if:
- the object already has a property defined with a name you are trying to set, and this property is not public
- if the magic setter fails for some reason
- if you're on an older version than 1.7.2

So your issue is the last bullet. The 1.7.2. changelog mentions:

Orm: You can now pass custom data when forging an ORM object.

which is what you do when you run the result of a join through as_object(). Can you upgrade to 1.7.2 (as a test only the ORM package will do), and see if that fixes it?

FuelPHP Forums

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

Howdy, Stranger!

Categories

In this Discussion