FuelPHP Forums

Mike

I'm having difficulty getting the correct info out of the ORM when using relations and limits. Here's an example: $posts = Model_Discount::find()
->related('blog');
->where('is_visible',1)
->where('blog.is_visible',1)
->order_by('popularity','desc')
->limit(10) I want that to show 10 posts from visible blogs. In this example it is only returning 8. And here's an abridged version of the SQL it's generating: SELECT ...
FROM (SELECT ... FROM `posts` AS `t0` WHERE `t0`.`is_visible` = 1 ORDER BY popularity DESC LIMIT 10) AS `t0`
LEFT JOIN `blogs` AS `t1` ON (`t0`.`blog_id` = `t1`.`id`)
WHERE `t1`.`is_visible` = 1 It looks like it's finding 10 posts, and then checking to see if the blog is visible, which means it's very common that it's going to return less than 10 rows. Has anyone run into anything similar? Any ideas how to fix it? Thanks Mike

Jelmer Schreuder

Added methods Orm\Query::rows_limit() and Orm\Query::rows_offset() which are keys 'rows_limit' and 'rows_offset' in array usage. Work both exactly the same as their non-rows_-prefixed counterparts. Note: when no subquery is used and there's both a limit & a rows_limit/offset, the rows_limit/offset will overwrite the normal limit/offset.

ushi

Thank you!

Mike

I know everyone's very busy but I'm still struggling with this. Have you had chance to think about the best way the limit choices should be implemented?

Rob McCann

I'm also having trouble with this... below is an example of where it becomes an issue

$result = \News\Model_News::find('all',array(
 'related' => array(
  'tags' => array(
   'where' => array(
    array('tag' , '=' , $tag)
   )
  )
 ),
 'limit'=>10
));

This looks for the first 10 items in the news table, if any of those 10 have the tag, it returns it. What it needs to do is to look for all items with the tag and return the top 10. Would it be better to return all the results and use array_split to limit or to manually write the query?

Jelmer Schreuder

Check my earlier comment on the issue as to why this solution was chosen and what other possibilities there are. Currently I have very little time and as no one ever created an issue about this it's not anywhere near my radar once I have more time. I am of course open to solutions and pull-requests, but I won't break backwards compatibility in any case.

Rob McCann

Would it make sense to do

'limit'=>array('inner'=>10, 'outer'=>5)

I know it's not very SQL-like but it could work

'limit'=>10

would be the same as

'limit'=>array('inner'=>10)

(p.s. should I create an issue on the ORM git hub and discuss it there? I dont want to spend time doing it if I'm going down the wrong path

)

Jelmer Schreuder

Yeah, just refer tot the topic in your issue. Bottom line is: issues I don't forget, topics I forget after replying. Important is that you don't take the array input as the default. Method chaining off the Query object is the primary way and default, the array input for Model::find() is just there for convenience. Thus any proposal should be to do Model::query()->limit(array('inner' => 10, 'outer' => 5)) and after that check if the array usage still works. I think this might work, though I'm not yet sure about the wording. With inner/outer it isn't immediately clear what it means. The normal limit(10) would be limiting the number of base models returned, while the "outer" limit would limit the number of database rows.
A better way might be to add a new method ->max_rows() that sets the maximum number of rows that is fetched from the DB, thus keep limit to limit the amount of base models returned while max_rows is the technical way of limiting the number of database rows that is returned. That would add a new key array('max_rows' => 10) to the array usage.

ushi

is there any solution today?

Rob McCann

I'm happy to look into this when I get a few moments. I'll post the issue url here once I've set it up. In the mean time, feel free to set it up yourself.

ushi

ok, thanks

Jelmer Schreuder

Hmm, that condition should go into the subquery not the outer one. Are you sure you're up to date with RC3? Otherwise it's probably a bug. Report it on https://github.com/fuel/orm/issues after you've checked you're working with Fuel RC3.

Mike

I've just tried the development branch from Github and still having the same issue. Seems like a pretty big bug - I'd be surprised if it's not something I'm doing wrong.

Jelmer Schreuder

Ahh, I misread your SQL the first time. We limit the amount of the object requested because that'll lead to the best results most of the time, and is right from a logical point of view. This also leads to the best results when doing a query with a many-many relation (because you can actually limit those, contrary to most other implementations). The problem here is that for you this is a problem because you want to disable by both the main and the related object. One way to solve this might be to add the conditions on the relation to the JOIN ...ON() part of the query I think. Got to think about how to best handle this.

Julian Esperat

I just had this problem too. I don't see any reason to use the standard ->limit() and ->offset() functions. It's never what I want. I'll just always use ->rows_limit() and ->rows_offset()...

Mike

The part that doesn't seem logical to me, if without a limit it returns lots of rows, then having a limit of 10 returns less than 10.

Jelmer Schreuder

Study the SQL, the problem is that there's a couple of options and the Orm needs to choose one: - limit by the original model (how our Orm works)
Makes it possible to limit one->many & many->many, otherwise impossible. Might return less than the limit when a condition on a relation eliminates rows. - limit like above but put the where condition in the ON clause of the LEFT JOIN
This will return the 10 objects but might keep some relations empty while not eliminating the rows that the condition works on. - put the limit on the entire query
This would give the expected result for your example, but make it impossible to limit one->many and many->many queries. I would like to find a solution for this, maybe allow the dev to choose but I'm not yet sure how that's best implemented.

FuelPHP Forums

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

Howdy, Stranger!

Categories

In this Discussion