ORM: List of items with specific tag

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Tue, 2012-09-04 23:06

I'm having a difficult time pulling a list of all items that have a particular tag--even when on the tag's page.

This works:

ORM::factory("item")
->join("items_tags", "items.id", "items_tags.item_id")
->where("items_tags.tag_id", "=", $theme->tag->id)
->count_all();

This doesn't:

ORM::factory("item")
->join("items_tags", "items.id", "items_tags.item_id")
->where("items_tags.tag_id", "=", $theme->tag->id)
->find_all();

Anyone see what I'm missing here?

floridave

Joined: 2003-12-22
Posts: 27300

Posted: Wed, 2012-09-05 00:22

ORM::factory("tag")
      ->join("items_tags", "tags.id", "items_tags.tag_id", "left")
      ->where("items_tags.item_id", "=", $item->id)
      ->find_all();

or better yet this is how the about this photo module works:
$something = tag::item_tags($theme->item());

Hope that helps.

Dave

_____________________________________________
Blog & G2 || floridave - Gallery Team

floridave

Joined: 2003-12-22
Posts: 27300

Posted: Wed, 2012-09-05 01:36

rereading your question I might have miss-understood.
Let me look a bit deeper

Dave

_____________________________________________
Blog & G2 || floridave - Gallery Team

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Wed, 2012-09-05 20:07

Thanks for looking into it.

I've tried a million variations, but I can't get it to pull info from a field on the second table; it's basically like they're not joined. Fields from the 1st table are no problem and (for some reason) counting instances of a field on the 2nd table seems to work, but pulling a list of results seems to be a no-go. Still trying to get it worked out.

EDIT: Looking at the code you posted, I assume that's for looking up all of the tags that are assigned to an item? If so, I can't get that to work either.

I'm likely making the same mistake with both case scenarios.

Is there a simple way to say take this [item_id], go to [items_tags] and return each [tag_id] associated with that item? Where I'm getting stuck: I can't seem to get any information that's not stored in the [items] table. I've tried variations of both ORM:: and DB:: and nothing's doing it for me. I also tried tag::item_tags($theme->item()), but can't get it to work when given a specific [item_id] (that may or may not have anything to do with the page the user is actually on at the time). Again, counting items isn't a problem. The sticking point is pulling items from a column into a new array.

Any help is greatly appreciated. (The kohana docs haven't done much for me, but I'm still looking.)

floridave

Joined: 2003-12-22
Posts: 27300

Posted: Wed, 2012-09-05 20:14

I'm no DB expert and table joins is magic to me.
Are you trying to get all the items with a specific tag name, given the tag name?

Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Wed, 2012-09-05 20:19

Yes, that was the initial goal: to pull all items (itemIDs) that have a specific tag associated, given the tagID.
I'll then use those itemIDs to pull other info.

rWatcher

Joined: 2005-09-06
Posts: 722

Posted: Fri, 2012-09-07 15:50

I think you want something like this, where $tag_id is the id number to the tag.

      $items = ORM::factory("item")
               ->viewable()
               ->join("items_tags", "items.id", "items_tags.item_id")
               ->where("items_tags.tag_id", "=", $tag_id)
               ->find_all();

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Fri, 2012-09-07 16:32

@rWatcher: Many thanks; this has been driving me crazy for a week now. That's what I initially tried (see 1st post); it doesn't work for me even if I enter the specific tagID instead of a variable. I didn't include the viewable parameter because I don't have any hidden items/permissions in my Gallery. Any thoughts?

rWatcher

Joined: 2005-09-06
Posts: 722

Posted: Fri, 2012-09-07 17:51

I copied that code out of the Tag Albums controller file, so it should work. Where are you trying to run the code from -- a controller, a view, a helper file, etc? Also, I assume you're assigning the result to a variable (the code in the first post doesn't). Might help if I could see exactly what you were doing.

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Sat, 2012-09-08 16:21

Tag albums may be different because it's a module; there are other pieces of code that I've noticed are significantly different within modules than outside, not sure why.

Yes it's assigned to a variable.

The file types listed shouldn't matter too much for a db query (I have successful queries running in different types), but I've tried them all. (Maybe the difference here is the join? Would that be prohibited or otherwise ineffective in some file types? Doesn't seem logical, but none of my "logical" ideas are working out right now, so maybe?)

As mentioned, the query can count the items, just not populate the results of find_all().

Exactly what I'm trying to do (per @floridave): "trying to get all the items with a specific tag name, given the tag name" (or, in this case, the tagID, but I could make either way work)

The issues here appears to be something about the way join works. I'm able to pull any info off of the first table, but nothing off of the second.

I'm likely missing something that should've been obvious to me, but I'm really stuck on this one. Ideas?

rWatcher

Joined: 2005-09-06
Posts: 722

Posted: Thu, 2012-09-13 19:08

tempg wrote:

Tag albums may be different because it's a module; there are other pieces of code that I've noticed are significantly different within modules than outside, not sure why.

The following code runs fine for me from within themes/wind/views/page.html.php. It grabs every item from the database tagged with Tag ID # 48, prints out the total number of items (via count()) and then displays the title of each photo. I've tested it with and without the viewable parameter, and it works fine either way.

<?
  $items = ORM::factory("item")
               ->viewable()
               ->join("items_tags", "items.id", "items_tags.item_id")
               ->where("items_tags.tag_id", "=", 48)
               ->find_all();
  print "Total Items: " . count($items) . "<br/><br/>"; // Print the total number of photos.
  foreach ($items as $item) {
    print "Title -- " . $item->title . "<br/>"; // Print the title of each photo.
  }
?>

Is it possible that there are so many results from find_all() that something on the server is timing out? You could try something like this, which only retrieves the first 10 items tagged with Tag ID #48 to test:

<?
  $items = ORM::factory("item")
               ->viewable()
               ->join("items_tags", "items.id", "items_tags.item_id")
               ->where("items_tags.tag_id", "=", 48)
               ->find_all(10, 0);
  print "Total Items: " . count($items) . "<br/><br/>"; // Print the total number of photos.
  foreach ($items as $item) {
    print "Title -- " . $item->title . "<br/>"; // Print the title of each photo.
  }
?>

tempg

Joined: 2005-12-17
Posts: 1857

Posted: Wed, 2012-10-03 17:45

@rWatcher:
Many thanks for taking the time to go into detail on this. I've been trying basically the code thing you posted, to no avail; I tried using your exact code and it still didn't work--at all!

SOLUTION: After trying a million different variations on the same code, I finally realized the issue: The code wasn't working because some of the album names/titles have (unencoded?) single and double quotes in them that broke the print/echo code. This is why I could use count_all, but not find_all.

I'm not sure why it broke here and not in other places, but I suspect that Gallery encodes those characters somewhere in a way that I need to duplicate. For now, I've just removed the single quotes from the titles. (You can leave one or the other, but not both; which depends on now you code the print/echo statement.)

Again, many thanks to @rWatcher; I might never have otherwise solved this if I didn't come to know that it worked for you. (I was thinking the issue was in the ORM statement and don't know that I would have moved beyond thinking that anytime soon.)

tempg Joined: 2005-12-17 Posts: 1857	Posted: Tue, 2012-09-04 23:06
	I'm having a difficult time pulling a list of all items that have a particular tag--even when on the tag's page. This works: ORM::factory("item") ->join("items_tags", "items.id", "items_tags.item_id") ->where("items_tags.tag_id", "=", $theme->tag->id) ->count_all(); This doesn't: ORM::factory("item") ->join("items_tags", "items.id", "items_tags.item_id") ->where("items_tags.tag_id", "=", $theme->tag->id) ->find_all(); Anyone see what I'm missing here?
	XUser login Username: * Password: * Create new account Request new password
floridave Joined: 2003-12-22 Posts: 27300	Posted: Wed, 2012-09-05 00:22
	ORM::factory("tag") ->join("items_tags", "tags.id", "items_tags.tag_id", "left") ->where("items_tags.item_id", "=", $item->id) ->find_all(); or better yet this is how the about this photo module works: `$something = tag::item_tags($theme->item());` Hope that helps. Dave _____________________________________________ Blog & G2 \|\| floridave - Gallery Team

floridave Joined: 2003-12-22 Posts: 27300	Posted: Wed, 2012-09-05 01:36
	rereading your question I might have miss-understood. Let me look a bit deeper Dave _____________________________________________ Blog & G2 \|\| floridave - Gallery Team

tempg Joined: 2005-12-17 Posts: 1857	Posted: Wed, 2012-09-05 20:07
	Thanks for looking into it. I've tried a million variations, but I can't get it to pull info from a field on the second table; it's basically like they're not joined. Fields from the 1st table are no problem and (for some reason) counting instances of a field on the 2nd table seems to work, but pulling a list of results seems to be a no-go. Still trying to get it worked out. EDIT: Looking at the code you posted, I assume that's for looking up all of the tags that are assigned to an item? If so, I can't get that to work either. I'm likely making the same mistake with both case scenarios. Is there a simple way to say take this [item_id], go to [items_tags] and return each [tag_id] associated with that item? Where I'm getting stuck: I can't seem to get any information that's not stored in the [items] table. I've tried variations of both `ORM::` and `DB::` and nothing's doing it for me. I also tried `tag::item_tags($theme->item())`, but can't get it to work when given a specific [item_id] (that may or may not have anything to do with the page the user is actually on at the time). Again, counting items isn't a problem. The sticking point is pulling items from a column into a new array. Any help is greatly appreciated. (The kohana docs haven't done much for me, but I'm still looking.)

floridave Joined: 2003-12-22 Posts: 27300	Posted: Wed, 2012-09-05 20:14
	I'm no DB expert and table joins is magic to me. Are you trying to get all the items with a specific tag name, given the tag name? Dave _____________________________________________ Blog & G2 \|\| floridave - Gallery Team

tempg Joined: 2005-12-17 Posts: 1857	Posted: Wed, 2012-09-05 20:19
	Yes, that was the initial goal: to pull all items (itemIDs) that have a specific tag associated, given the tagID. I'll then use those itemIDs to pull other info.

rWatcher Joined: 2005-09-06 Posts: 722	Posted: Fri, 2012-09-07 15:50
	I think you want something like this, where $tag_id is the id number to the tag. $items = ORM::factory("item") ->viewable() ->join("items_tags", "items.id", "items_tags.item_id") ->where("items_tags.tag_id", "=", $tag_id) ->find_all();

tempg Joined: 2005-12-17 Posts: 1857	Posted: Fri, 2012-09-07 16:32
	@rWatcher: Many thanks; this has been driving me crazy for a week now. That's what I initially tried (see 1st post); it doesn't work for me even if I enter the specific tagID instead of a variable. I didn't include the viewable parameter because I don't have any hidden items/permissions in my Gallery. Any thoughts?

rWatcher Joined: 2005-09-06 Posts: 722	Posted: Fri, 2012-09-07 17:51
	I copied that code out of the Tag Albums controller file, so it should work. Where are you trying to run the code from -- a controller, a view, a helper file, etc? Also, I assume you're assigning the result to a variable (the code in the first post doesn't). Might help if I could see exactly what you were doing.

tempg Joined: 2005-12-17 Posts: 1857	Posted: Sat, 2012-09-08 16:21
	Tag albums may be different because it's a module; there are other pieces of code that I've noticed are significantly different within modules than outside, not sure why. Yes it's assigned to a variable. The file types listed shouldn't matter too much for a db query (I have successful queries running in different types), but I've tried them all. (Maybe the difference here is the join? Would that be prohibited or otherwise ineffective in some file types? Doesn't seem logical, but none of my "logical" ideas are working out right now, so maybe?) As mentioned, the query can count the items, just not populate the results of find_all(). Exactly what I'm trying to do (per @floridave): "trying to get all the items with a specific tag name, given the tag name" (or, in this case, the tagID, but I could make either way work) The issues here appears to be something about the way join works. I'm able to pull any info off of the first table, but nothing off of the second. I'm likely missing something that should've been obvious to me, but I'm really stuck on this one. Ideas?

rWatcher Joined: 2005-09-06 Posts: 722	Posted: Thu, 2012-09-13 19:08
	tempg wrote: Tag albums may be different because it's a module; there are other pieces of code that I've noticed are significantly different within modules than outside, not sure why. The following code runs fine for me from within themes/wind/views/page.html.php. It grabs every item from the database tagged with Tag ID # 48, prints out the total number of items (via count()) and then displays the title of each photo. I've tested it with and without the viewable parameter, and it works fine either way. <? $items = ORM::factory("item") ->viewable() ->join("items_tags", "items.id", "items_tags.item_id") ->where("items_tags.tag_id", "=", 48) ->find_all(); print "Total Items: " . count($items) . "<br/><br/>"; // Print the total number of photos. foreach ($items as $item) { print "Title -- " . $item->title . "<br/>"; // Print the title of each photo. } ?> Is it possible that there are so many results from find_all() that something on the server is timing out? You could try something like this, which only retrieves the first 10 items tagged with Tag ID #48 to test: <? $items = ORM::factory("item") ->viewable() ->join("items_tags", "items.id", "items_tags.item_id") ->where("items_tags.tag_id", "=", 48) ->find_all(10, 0); print "Total Items: " . count($items) . "<br/><br/>"; // Print the total number of photos. foreach ($items as $item) { print "Title -- " . $item->title . "<br/>"; // Print the title of each photo. } ?>

tempg Joined: 2005-12-17 Posts: 1857	Posted: Wed, 2012-10-03 17:45
	@rWatcher: Many thanks for taking the time to go into detail on this. I've been trying basically the code thing you posted, to no avail; I tried using your exact code and it still didn't work--at all! SOLUTION: After trying a million different variations on the same code, I finally realized the issue: The code wasn't working because some of the album names/titles have (unencoded?) single and double quotes in them that broke the print/echo code. This is why I could use count_all, but not find_all. I'm not sure why it broke here and not in other places, but I suspect that Gallery encodes those characters somewhere in a way that I need to duplicate. For now, I've just removed the single quotes from the titles. (You can leave one or the other, but not both; which depends on now you code the print/echo statement.) Again, many thanks to @rWatcher; I might never have otherwise solved this if I didn't come to know that it worked for you. (I was thinking the issue was in the ORM statement and don't know that I would have moved beyond thinking that anytime soon.)

ORM: List of items with specific tag

XUser login