FuelPHP Forums

Jume

I have this kind of find on ORM model:

  $query = Model_Article::find()
     ->related('author')
     ->where('title', 'like', $search)
     ->or_where('summary', 'like', $search)
     ->or_where('body', 'like', $search);

How could I add where condition on 'name' filed in related 'author' model? Simply using ->or_where('name', 'like', $search) wont works because it will assume name is a filed in article model... Thanks,
Jume

WanWizard

Like all other items that have a notion of hierarchy, ORM also uses dot-notation:

->or_where('author.name', 'like', $search)

Jume

I tried this but it does not work! I get the SQL error, check below!

$query = Model_Article::find()
   ->related('author')
   ->where('articles.title', 'like', '%' . $search .'%')
   ->or_where('articles.summary', 'like', '%' . $search .'%')
   ->or_where('articles.body', 'like', '%' . $search .'%')
   ->or_where('authors.name', 'like', '%' . $search .'%');

I get this SQL error: Fuel\Core\Database_Exception [ Error ]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'articles.title' in 'where clause' with query: "SELECT COUNT(DISTINCT `t0`.`id`) AS count_result, `t1`.`id` AS `t1_c0`, `t1`.`name` AS `t1_c1`, `t1`.`gender` AS `t1_c2`, `t1`.`description` AS `t1_c3`, `t1`.`country_id` AS `t1_c4`, `t1`.`created_at` AS `t1_c13`, `t1`.`updated_at` AS `t1_c14` FROM `articles` AS `t0` LEFT JOIN `providers` AS `t1` ON (`t0`.`author_id` = `t1`.`id`) WHERE `articles`.`title` LIKE '%Search%' OR `articles`.`summary` LIKE '%Search%' OR `articles`.`body` LIKE '%Search%' `author`.`name` LIKE '%Search%'"

WanWizard

Eh? Articles is your main model, why prefix it? You asked the question about related models, articles is not a related model. where('title', 'like', ...) should do just fine. You only need the prefix for "author.name" to indicate name is a column in the related author model.

Jume

Harro Verton wrote on Friday 4th of May 2012:

Eh? Articles is your main model, why prefix it? You asked the question about related models, articles is not a related model. where('title', 'like', ...) should do just fine. You only need the prefix for "author.name" to indicate name is a column in the related author model.

Yes you are right, no need to prefix mani model and only use author.field_name instead authors.field_name... This now works, but if I add limit and offset into the mix, then it always returns empty record set.

$query = Model_Article::find()
                  ->related('author') 
                  ->where('title', 'like', '%' . $search .'%') 
                  ->or_where('summary', 'like', '%' . $search .'%') 
                  ->or_where('body', 'like', '%' . $search .'%') 
                  ->or_where('author.name', 'like', '%' . $search .'%')
                  ->limit(Mypagination::$per_page)
                  ->offset(Mypagination::$offset)
                  ->get();

This will always return emty recordset, even if $search matches some records. If I remove limit and offset it will return me the right records. Any idea why would limit/offset do this to the query? It doesn't matter which limit/offset values I use, it's always the same.

Jume

In case of adding limit/offset the output SQL is kind of strange... it ads a nested SELECT into FROM clause!? SELECT `t0`.`id` AS `t0_c0`, `t0`.`tile` AS `t0_c1`, `t0`.`summary` AS `t0_c2`, `t0`.`body` AS `t0_c3`, `t0`.`tags` AS `t0_c4`, `t0`.`author_id` AS `t0_c5`, `t0`.`created_at` AS `t0_c6`, `t0`.`updated_at` AS `t0_c7`, `t1`.`id` AS `t1_c0`, `t1`.`name` AS `t1_c1`, `t1`.`gender` AS `t1_c2`, `t1`.`country_id` AS `t1_c3` FROM (SELECT `t0`.`id` AS `t0_c0`, `t0`.`tile` AS `t0_c1`, `t0`.`summary` AS `t0_c2`, `t0`.`body` AS `t0_c3`, `t0`.`tags` AS `t0_c4`, `t0`.`created_at` AS `t0_c5`, `t0`.`updated_at` AS `t0_c6`FROM `articles` AS `t0` WHERE `t0`.`title` LIKE '%Gali%' OR `t0`.`summary` LIKE '%Gali%' OR `t0`.`body` LIKE '%Gali%' ORDER BY `t0`.`id` DESC LIMIT 25 OFFSET 0) AS `t0` LEFT JOIN `authors` AS `t1` ON (`t0`.`author_id` = `t1`.`id`) WHERE `t1`.`name` LIKE '%Gali%' ORDER BY `t0`.`id` DESC

WanWizard

Search for that, this has been answered numerous times. An ORM is not a query builder. Which means in a relation hierarchy a limit() doesn't limit on rows, it limits on resultsets, where it will make sure the resultsets are complete and consistent. For example, if you have 2 parents, each have 8 children, and you run a normal JOIN with a limit(10), you will get the first parent with all 8 children, plus the second parent and 2 of it's 8 children. So the result is incomplete and there not consistent. the ORM refuses to run such queries. In this example, ORM will return 16 records, even with a limit of 10.

FuelPHP Forums

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

Howdy, Stranger!

Categories

In this Discussion