Multiple JOIN's with same table

[katyo]

I have a table EntryProperty which implements many-to-many relation between Entry and Property.

Entry
    …

Property
    …

EntryProperty
    entry EntryId
    property PropertyId
    value Double
    UniqueEntryProperty entry property

I need to do queries with INNER JOINs between one Entry and some EntryPropertys (zero to five), like this:

SELECT entry FROM entry AS e
  INNER JOIN entry_property AS p1 ON p1.property = p_1 AND p1.entry = e.id AND p1.value > v_1
  INNER JOIN entry_property AS p2 ON p2.property = p_2 AND p2.entry = e.id AND p2.value = v_2
  … WHERE …

My first solution looks like so (yesod get request handler):

getEntriesWithPropertiesR from cnt props =
  respondSourceDB "application/json" $
    (E.selectSource $ fromProps props)
    =$ awaitForever getEntry $= streamJSONArray
  where
    fromProps [] = E.from $ queryEntry

    fromProps (p_1:[]) =
      E.from $ \(e `E.InnerJoin` p1) ->
                queryProp p_1 e p1 >>
                queryEntry e

    fromProps (p_1:p_2:[]) =
      E.from $ \(e
                 `E.InnerJoin` p1
                 `E.InnerJoin` p2) ->
                queryProp p_1 e p1 >>
                queryProp p_2 e p2 >>
                queryEntry e

    fromProps (p_1:p_2:p_3:[]) =
      E.from $ \(e
                 `E.InnerJoin` p1
                 `E.InnerJoin` p2
                 `E.InnerJoin` p3) ->
                queryProp p_1 e p1 >>
                queryProp p_2 e p2 >>
                queryProp p_3 e p3 >>
                queryEntry e

    fromProps (p_1:p_2:p_3:p_4:[]) =
      E.from $ \(e
                 `E.InnerJoin` p1
                 `E.InnerJoin` p2
                 `E.InnerJoin` p3
                 `E.InnerJoin` p4) ->
                queryProp p_1 e p1 >>
                queryProp p_2 e p2 >>
                queryProp p_3 e p3 >>
                queryProp p_4 e p4 >>
                queryEntry e

    fromProps (p_1:p_2:p_3:p_4:p_5:_) =
      E.from $ \(e
                 `E.InnerJoin` p1
                 `E.InnerJoin` p2
                 `E.InnerJoin` p3
                 `E.InnerJoin` p4
                 `E.InnerJoin` p5) ->
                queryProp p_1 e p1 >>
                queryProp p_2 e p2 >>
                queryProp p_3 e p3 >>
                queryProp p_4 e p4 >>
                queryProp p_5 e p5 >>
                queryEntry e

    queryProp (PropCond prop_id prop_cond) e p = do
      E.on $ p E.^. EntryPropertyProperty E.==. E.val prop_id
        E.&&. p E.^. EntryPropertyEntry E.==. e E.^. EntryId
        E.&&. p E.^. EntryPropertyValue `propCond` prop_cond

    queryEntry e = do
      E.orderBy [E.desc $ e E.^. EntryCreated]
      E.limit $ fromIntegral cnt
      E.offset $ fromIntegral $ from * cnt
      return e

    getEntry = …
    propCond = …

This works perfect but only with zero and one entry_property. For two and more it throws an sql error:

Unknown column 'entry_property2.property' in 'on clause'

Esqueleto version is 2.1.1

[meteficha]

Did you try reversing the order of the queryProps? E.g.:

    fromProps (p_1:p_2:p_3:p_4:p_5:_) =
      E.from $ \(e
                 `E.InnerJoin` p1
                 `E.InnerJoin` p2
                 `E.InnerJoin` p3
                 `E.InnerJoin` p4
                 `E.InnerJoin` p5) ->
                queryProp p_5 e p5 >>
                queryProp p_4 e p4 >>
                queryProp p_3 e p3 >>
                queryProp p_2 e p2 >>
                queryProp p_1 e p1 >>
                queryEntry e

[katyo]

Hmm, It seems to be working now. Thanks.

[katyo]

Is there any way to do multiple joins like this at runtime?

[meteficha]

Yes, if you use implicit inner joins (i.e. independent froms, joining via where). Which would work for you since you're using inner joins, but doesn't work in the general case.

I'm going to close this issue then :).

[katyo]

Ok, thanks :)