Add support for intersect

[Jaymon]

An intersect query:

        SELECT 
            "_id"
        FROM 
            "foo"
        WHERE 
            ("type" = 'a' AND "value" = '1')
         INTERSECT
        SELECT 
            "_id"
        FROM 
            "foo"
        WHERE 
            ("type" = 'b' AND "value" = '2')

and a count query:

SELECT count(*) FROM (
        SELECT 
            "_id"
        FROM 
            "foo"
        WHERE 
            ("type" = 'a' AND "value" = '1')
         INTERSECT
        SELECT 
            "_id"
        FROM 
            "foo"
        WHERE 
            ("type" = 'b' AND "value" = '2')
) I

This could be done in prom:

q1 = Foo.query.select_pk().eq_type("a").eq_value("1")
q2 = Foo.query.select_pk().eq_type("b").eq_value("2")

# get the results
Foo.query.intersect(q1, q2).all()

# count the results
Foo.query.intersect(q1, q2).count()

References

• use count function with intersect
• SQL intersect operator

Search

• sql postgres intersect count query

[Jaymon]

We could also have a shortcut syntax:

Foo.query.intersect_pk(
  [("type", "a"), ("value", "1")],
  [("type", "b"), ("value", "2")],
)

Notice that intersect_pk that is what field should be selected. Not sure how to do it to select multiple fields

[Jaymon]

Limit and offset syntax:

SELECT * FROM (
        SELECT 
            "_id"
        FROM 
            "foo"
        WHERE 
            ("type" = 'a' AND "value" = '1')
         INTERSECT
        SELECT 
            "_id"
        FROM 
            "foo"
        WHERE 
            ("type" = 'b' AND "value" = '2')
) I LIMIT 5 OFFSET 0

There is also UNION and UNIONALL you could use in place of INTERSECT, all the syntax seems to be the same

[Jaymon]

If the individual queries need to be limited or anything, they should be enclosed in parens:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10)