I'm building a site that has a tagging based on the toxi schema. But I'm having difficulties generating a query that fetches links/bookmarks based on their tag. Using "Toxi" the links/bookmarks table does not contain any tag id info, instead that info is store in the tag_map. I need to form a query where I can display links that match a particular tag_id (via tag_map). Can anyone point me in the right direction?
`links`
`link_id`
`link_title`
`link_owner_id`
`link_url`
`link_description`
`link_datecreated`
`tags`
`tag_id`
`tag_title`
`tag_description`
`tag_owner_id`
`tag_map`
`map_id`
`map_link_id`
`map_tag_id`
SELECT l.*
FROM tag_map tm
JOIN links l
ON l.link_id = tm.map_link_id
WHERE tm.map_tag_id = @my_tag_id
If you use InnoDB engine, it's not good decision to do not use relation(relation to link into tag).
relation always contains your data consistency
But If you also want to choose without relation, you can use Transaction or trigger for insertion data
then Try the following query as follows for retrieving data
SELECT tag_map.map_id,tags.tag_title,links.link_title FROM tag_map
LEFT JOIN tags
ON tag_map.map_tag_id=tags.tag_id
LEFT JOIN links
ON tag_map.map_link_id=links.link_id