Friday, 15 May 2015

Boolean expressions for a tagging system in SQL -


To keep this SQL Tables for the tagging system: 

  create table tag (id serial primary)

The user's Boolean expression query in SQL is " Tag 1 and tag2 ": 

  SELECT items. * Inner joins items from items tagged_items as i1 (items.id = i1.item_id) on interiors (i1.tag_id = t1.id) included as inline tag t1 included as i2 on tagged_items (items.id = I2.item_id) Tagged as JOIN (I2.tag_id = t2.id) on the inside where T1name = 'tag1' and 't2.name =' tag2 ';

How do you translate other questions with Boolean expressions like " Tag 1 or Tag2 and Tag 3 " ... or more complex questions like " Tag1 and (Tag2 or Tag3) and not Tag4 or Tag 5 "From SQL?

Assume that data -> items, words -> names and tagged_item -.> Tagged_items

This is for " tag1 and (tag2 or tag3) and tag4 or tag5 ". I'm sure you can find out the rest. 

  SELECT items. * Skip items from (select I1.item_id i1 INNER AS i1 INNER i1.tag_id = t1 as the tag ID and t1.name = 'tag1') as ti1 on items.id = ti1. Item_id Left Join (i2.item_id from tagged_items as i2.tag_id = t2.id and t2.name tag as in i2 interiose selection = 'tag2') as ti2 on items.id = ti2.item_id I3.tag_id = t3.id and t3.name = 'tag3' on the left join (as tagged as i3.item_id as i3.item_id selection in the form of i3.item_id as the T3), on the objects as ti3 .id = ti3.item_id Left join (i4.titem_id from tagged_items as i4.t Ag_id = t4.id and t4.name = 'tag4' tags as T-4) as items ti4 at items.id = ti4.item_id Left JOIN (select tags i5.item_id from tag_items i5 in the form of tag t5 I5.tag_id = t5.id and t5.name = 'tag5') in ti5 as items.id = ti5.item_id Where ti1.item_id is not zero and (Ti2.item_id is not null or not ti3.item_id Is) and ti4.item_id is not faucet or ti5.item_id;  Edit:  If you want to avoid subclasses, you can: 
 
  SELECT items. * Leave the tag on item IIT ON I1 ON items.id = i1.item_id Add tag as i1.tag_id = t1.id and t1.name = 'tag1' ... WHERE t1.item_id faucet ... I do not believe that you want to do this because because of the addition of extra left, there will be a slow result.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)