MongoDB Is Abusing JSON

Re:MongoDB Is Abusing JSON by SigmundA on Thursday January 17, 2013 @08:46PM (#5076580)

  SELECT * FROM post 
  WHERE id IN
  (
     select post_id from post_tags
     INNER JOIN tags ON post_tag.tag_id = tag.id
     WHERE tag.tag in ("foo","bar","baz")
     GROUP BY post_id
     HAVING COUNT(*) = 3
  )
  AND author IN ("Joe","Jane")
  ORDER BY post_date DESC;

Of course normalizing tags is silly as they are natural keys so it should be as you first stated:

  SELECT * FROM post 
  WHERE id IN
  (
     select post_id from post_tags
     WHERE tag in ("foo","bar","baz")
     GROUP BY post_id
     HAVING COUNT(*) = 3
  )
  AND author IN ("Joe","Jane")
  ORDER BY post_date DESC;

[ Parent | on HN ]

Re:MongoDB Is Abusing JSON by cheald on Thursday January 17, 2013 @08:55PM (#5076601)

Are subselects still ridiculously inefficient in MySQL? It's been a long while since I've used them. I do like how those queries read, though.

[ Parent | on HN ]

Re:MongoDB Is Abusing JSON by SigmundA on Thursday January 17, 2013 @09:05PM (#5076640)

Sorry I only use proper DB's like PostgreSQL and MSSQL who optimizer's have no problems with this ;).

Also what I like about the count approach is you can do "show me all the posts with at least 2 out of the 3 tags matching" fuzzier search if you desire.

[ Parent | on HN ]