Query Builder

README.md

Quirrel

Goals

• Immutable
• Extensible
• Cover all of PostgreSQL's functionaliy
• Transpile SQL to Quirrel

example1.js

`
SELECT array_agg(players), player_teams
FROM (
  SELECT DISTINCT t1.t1player AS players, t1.player_teams
  FROM (
    SELECT
      p.playerid AS t1id,
      concat(p.playerid,':', p.playername, ' ') AS t1player,
      array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
    FROM player p
    LEFT JOIN plays pl ON p.playerid = pl.playerid
    GROUP BY p.playerid, p.playername
  ) t1
INNER JOIN (
  SELECT
    p.playerid AS t2id,
    array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
  FROM player p
  LEFT JOIN plays pl ON p.playerid = pl.playerid
  GROUP BY p.playerid, p.playername
) t2 ON t1.player_teams=t2.player_teams AND t1.t1id <> t2.t2id
) innerQuery
GROUP BY player_teams
`;

q
  .select(q.fn('array_agg', 'players'), 'player_teams')
  .from(
    [
      q
        .select({
          'p.playeid': 'tlid',
          [q.fn('concat', 'p.playerid', ':', 'p.playername', '')]: 'tlplayer',
          [q.fn('array_agg', 'pl.teamid', q.orderBy('pl.teamid'))]: 'player_teams'
        })
        .form('player', 'p')
        .leftJoin({'p.playerid': 't2id'}, 'p.playerid', 'pl.playerid')
        .groupBy('p.playerid', 'p.playername'),
      't1',
      q.innerJoin(
        {[q
          .select({
            'p.playerid': 't2id',
            [q.fn('array_agg', 'pl.teamid', q.orderBy('pl.teamid'))]: 'player_teams'
          })
          .from('player', 'p')
          .leftJoin({'plays': 'pl'}, 'p.playerid', 'pl.playerid')
          .groupBy('p.playerid', 'p.playername')
        ]: 't2'},
        q.and(['t1.player_teams', 't2.player_teams'], ['t1.tlid', '<>', 't2.t2id'])
      )
    ],
    'innerQuery'
  )
  .groupBy('player_teams');