FuelPHP Forums

MikeQ

Let's say I have Model_Post and it has many and belongs to many Model_Tag`s.

I need to find posts that have a tag with id 1.

Now I do this like that:

$posts = Model_Post::query()->related('tags')->where('t1_through.tag_id', 1)->get();

It looked like a (not very good) solution, until i noticed, that $post->tags gives me only one tag (fair enough)

Like a temporary solution I walk through posts, write their ids into the $ids array and then use another query with where('id', 'IN', $ids) statement.

I have two questions:

* Is there a more correct way to find posts by a tag

* if there isn't, does ORM support something like Post.includes(:tags) (generating 2 DB queries) like rails activerecord does?

PS: I did try to find this in these forums but I failed.

PPS: sorry for my bad English

PPPS: Fuel is awesome.

Harro

You should be able to just use

where('tags.id', '=', 1)

in your query. It's obvious it gives you only one tag, you select it on primary key, so there can only be one.

If you need posts for a tag, you need to run your query the other way around:

$tag = Model_Tags::query()->related('posts')->where('id,'=',1)->get();
var_dump($tag->posts);

MikeQ

get() did not work for me, i think, it should be get_one() ?

I have been thinking about quering the Model_Tag BUT. If i do so I think i won't be able to limit() and offset().
Also, this query was not meant just to select posts by tag. I wanted to make it combined with search by title. I used to do it that way:

$posts = Model_Post::query()->related('tags');
strlen($q) > 2 && $posts = $posts->where('name', 'LIKE', '%'.$q.'%');
isset($tag) && $posts = $posts->where('tags.id', $tag);
$posts = $posts->get();

But if i run the query from Model_Tag it becomes impossible to get posts by title if $tag is not set,
Right?

Thanks for your instant response BTW.

Harro

difference between get() and get_one() is that the first returns an array of results (always, also if there is only one), and the second adds a LIMIT 1, and always returns a single result (or null if none found).

If you have multiple criteria, I assume you want or_where()? adding additional where() would add an AND, and I think you want an OR.

If it is a many-to-many, you always have (possible) multiple results, no matter from which side you run the query. So it should always be get().

MikeQ

So can i limit() and offset() rows of joined table?

Harro

You want to limit what exactly? You don't limit a specific table, you limit the result set of a query.

ORM is designed to keep results consistent, so in a query that includes relations, by default it will either include a parent with all it's children, or not at all. But it will not include a partial result, a parent with some of it's children, because of a limit. This may cause a limit(10) to return more or less then 10 records, depending on how many children a parent has.

If you don't want that, but are interested in a precise row limit (for example for paginating a table), you need to use rows_limit() and rows_offset() instead of limit() and offset().

MikeQ

I am sorry I couldn't explain well what i exactly want.

I have a controller that gets posts performing filtering through posts, both by title and by tag_id. If both params are set then i want sql be with AND WHERE.

I also have pagination on search results, so i will call limit() and offset() afterwards.
now it's working like that:

I have in post model a method:

public static function search($q, $tag)
{
	$posts = Model_Post::query()->related('tags');
	strlen($q) > 2 && $posts = $posts->where('name', 'LIKE', '%'.$q.'%');
	isset($tag) && $posts = $posts->where('tags.id', $tag);
	$posts = $posts->get();

	$ids = array();

	foreach($posts as $post)
		$ids[] = $post->id; 

	return Model_Post::query()->related('tags')
		->where('id', 'IN', $ids);

}

then i call in my controller

$posts = Model_Post::search(Input::get('q'), Input::get('tag'))
			->limit(10)
			->offset(Input::get('skip'))
			->get();

and pass $posts to my view

I just wanted to ask how can i improve search method?

Harro

Why are you running the same query twice?

The second one, with the id's, returns exactly the same records as the first one.

public static function search($q, $tag)
{
	$posts = static::query()->related('tags');
	strlen($q) > 2 && $posts = $posts->where('name', 'LIKE', '%'.$q.'%');
	isset($tag) && $posts = $posts->where('tags.id', $tag);
	return $posts;
}

This should work fine too.

Note though that since you use related(), and you want pagination, you should call it like this:

$posts = Model_Post::search(Input::get('q'), Input::get('tag'))
			->rows_limit(10)
			->rows_offset(Input::get('skip'))
			->get();

otherwise you may not get the desired result, as ORM will retrieve complete posts->tags sets when you use limit().

MikeQ

I run the same query twice to have the complete list of related tags when $tag filter is set, as they will be passed and displayed in my view. otherwise it will obviously select only one tag with the specified id

FuelPHP Forums

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

Howdy, Stranger!

Categories

In this Discussion