| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11
Comments:
<0> i have 3 tables... tags, products and products_tags
<0> basically i want to find all products tagged with 1 and 2 in products_tags
<0> anyone have a good starting point of how to approach this query? i'm at a loss
<1> hatezilla: Are you saying there are multiple tags per product?
<0> exactly
<0> so there'd be 2 entries in products_tags
<2> JOIN?
<0> both with the same product_id, one having tag_id 1, the other tag_id 2
<1> hatezilla: JOIN the tags table with the products table twice, once for each tag.
<0> well, it could be N tags
<0> im just using 2 here as a simple example
<1> hatezilla: or use a GROUP BY to obtain the same information.
<2> how do the 3 tables relate?
<1> hatezilla: How do you know what criteria to use?
<0> products_tags joins em
<0> im p***ing in a list of tag id's
<0> so products and the tags table know nothing about each other
<0> products_tags has 2 columns, product_id and tag_id
<1> hatezilla: Then use a GROUP BY to aggregate the tags per product and count those records that match the tag list you provide.
<2> SELECT p.* FROM (products p INNER JOIN ON ? products_tags INNER JOIN ON ? tags INNER JOIN ON ?) WHERE tag_id = 1 AND tag_id = 2; ?
<0> hrmm....
<1> hatezilla: The multiple JOIN approach that I mentioned earlier is what Jivedue just showed. But you don't really want that.
<2> what is he trying to do?
<1> hatezilla: You only care that all chosen tags are found for each product.
<0> well im writing 2 queries but this one basically i want to get a list of all prodcuts tagged with the tag list i p*** in
<0> so say i p*** in 1, 2
<0> (tag 1 & 2)
<1> Jivedue: He has a list of chosen tags. He wants all products that have those tags.
<0> i want to get all products with those tags
<1> hatezilla: Just use the GROUP BY approach.
<0> ill try that
<1> hatezilla: count the distinct records per group having COUNT(*)=#_in_your_list_of_tags
<0> sorry im slow, still trying to get it
<0> how does count help me
<0> im p***ing in id's, not # of tags
<0> the inner join approach seems to make more sense to me
<1> hatezilla: SELECT ... FROM prod JOIN tags ON prod.id=tags.prod_id WHERE tags.id IN (list) GROUP BY prod.id having count(*)=<count of tags in your list>
<1> hatezilla: Yes, but the inner join will not scale well to large numbers of tags in your chosen list.
<0> ah
<2> yah, I so don't get what's going on, haha
<0> well im trying the query
<0> i only semi-get it as well
<2> well, I don't get what you want that it would involve a COUNT
<0> exactly
<0> i dont see where that comes into play
<1> hatezilla: You have N chosen tags.
<2> could you just paste your table structures and what you want somewhere?
<1> hatezilla: The count of tags found will match that count if you do this properly.
<0> the query seems to work
<1> hatezilla: Trust me. This is the general approach to solve your problem. If you have a very limits set of chosen tags, so that the JOIN would be small, you don't have to use the GROUp BY approach.
<0> i doubt its going to be monsterous
<0> thanks for the help id like to understand why this is better from a performance standpoint though
<1> hatezilla: But GROUP BY is the scalable approach to this.
<0> oh oh oh
<0> ok...so IN gets me all products tagged in that list
<1> hatezilla: You haven't given a hint as to the size of the chosen list of tags.
<0> and the group by makes sure that the tag count is = to my list basically
<1> Yes,
<0> xgc, let's say 5-10 is the maximum
<0> this is for an e-commerce store
<1> hatezilla: I don't think you want a JOIN of 11 tables.
<1> hatezilla: Not if there's a chance this would grow.
<0> ah, so for each tag it'd have to run a join basically
<2> why would you have to join 11 tables?
<0> so if i had 10 tags it'd have to join 11 times?
<1> Jivedue: The original approach (INNER JOIN) requires that he join once for each tag.
<2> what does your tags table look like?
<0> id, name
<2> uhhh
<1> hatezilla: Both approaches are algorithmically correct. I'll let you discuss this with Jivedue. If you have any other questions for me, let me know.
<2> Xgc: So basically you're doing that so that he can use IN and doesn't have to put a bunch of ANDs?
<1> hatezilla: I said JOIN 11 tables, not 11 JOINs.
<2> what I don't get is why you couldn't just do something like this:
<1> Jivedue: No. The GROUP BY allows him to do this with a single JOIN.
<0> seems pretty fast
<2> SELECT p.* FROM (products p INNER JOIN product_tags pt ON pt.product_id = p.id) WHERE pt.tag_id = 1 AND pt.tag_id = 2 GROUP BY pt.product_id;
<1> You basically place all tags (from the list) in groups per product and count them per product.
<2> why can't you just do something like that?
<1> Jivedue: I already gave the query.
<0> Jivedue: i think you can
<2> Xgc: and that didn't do what he wants?
<1> Jivedue: Yes. But he's asking why that is better than the 10 JOIN approach for a list of 10 tags.
<2> Xgc: I don't get what you mean by joining 10+ tables? anyway you do it should only take 2. You're just giving 10 different WHERE conditions
<1> Jivedue: and he was hearing you say you didn't understand how the GROUP BY / COUNT would help. SO he was confused.
<0> actually Jivedue that query doesn't work for multiple tags
<1> Jivedue: I understand both approaches. IN .vs. AND has nothing to do with this.
<0> 1 tag works, 2 no results
<2> OHHHHH
<0> thanks both for your help
<1> Jivedue: You need to group the results. Your query is not the solution.
<2> hold on, I see what's happening
<2> I finally get wth you're doing
<0> i couldn't have figured this out myself
<1> hatezilla: Hope you understood my comments.
<2> I just didn't see what the problem was
<0> yeah i think i get it now
<0> onto my 2nd query...
<0> :)
<0> lets say i have the same setup right
<2> you have a prodcuts table and a prducts_to_tags table, and you want all the products that match ALL tag ids given tag in the products_to_tags table?
<0> basically yes Jivedue
<2> I see
<0> so if someone tags "under $10" and "flowers" itll get flowers under $10
<0> all products with BOTH the tags
<2> that's a cool prob.
<0> heres a better one ;)
<0> lets say i have the same tables...products, products_tags, tags
<3> HI Everybody!
<0> i want to p*** in again, a list of tag_ids
<0> but i want to get back a list of tags that are ALSO tagged to the same products as the ones p***ed in
<3> I have a problem with mysql when make log in
<2> to find similar products?
<0> so if a product is tagged 1, 2, 3 and i p*** in 1 & 2 i get 3
<0> to find similar tags...
<0> so it knows, "ok there's a product tagged 1, 2 & 3...i get p***ed in 1 & 2, i need to p*** back out tag 3"
<2> k, so you'd use product_tags and tags then, right?
<0> i believe so
<0> probably similar to the first query then huh
<0> except COUNT could be greater
<0> Xgc: you get that one?
<3> yesterday, i Make aptitude upgrade, so now i cant log in ...
<2> k hatezilla, you just use the same query, but you do a LEFT JOIN
<0> Jivedue: the one you typed out?
<2> no, well, whatever what you end up using to get what you want
<1> hatezilla: That's pretty simple. The query will look complex.
<1> hatezilla: These are problems that are easy to solve. You just need to break them down and solve each subproblem separately.
<1> hatezilla: Then pull it all together into one complex query.
<2> you'll get a list of product_ids FROM that 1st query you worked on, right? So then you LEFT JOIN it on the product_tags table ON products.id = product_tags.product_id ..
<2> nm, you don't LEFT JOIN, just INNER JOIN
<2> ***uming you want 1,2, and 3 back
<0> yea
<0> just 3
<0> if i p*** in 1 & 2
<1> hatezilla: This starts out just like the last problem, with the same query to find products. Then find the list of tags for those products. Then subtract the initial tag list from this full list of tags.
<0> or if i p*** in 1 & 3 i want 2
<2> yah, exactly
<0> word
<2> but I'm still trying to think how to solve that 1st query a different way, haha
<0> that's all possible in 1 query?
<1> hatezilla: I'm not certain you really want this, however.
<0> or am i going to have to use a subselect
<1> hatezilla: Yes. It's possible.
<0> ok
<1> hatezilla: With 4.1 or better, you can do this in one query.
<0> yeah im on 4.1
<1> hatezilla: If you match more than one product, you want all the extra tags found in those products?
<0> yes
<1> Then my description above is what you want. Try that yourself.
<1> I'll be taking off soon.
Return to
#mysql
or
Go to some related
logs:
#lisp
grub install hpt374
arch-linux modular xorg xmessage
#perl
#linux
#css
#ubuntu
#php
xinetd :Kernel Panic - not syncing: Fatal exception
patching centos
|
|