title: SQLAlchemy, array_agg, and matching an input list
body: <p>I am attempting to use SQLAlchemy more fully, rather than just falling back to pure SQL at the first sign of distress.  In this case, I have a table in a Postgres database (9.5) which stores a set of integers as a group by associating individual items <code>atom_id</code> with a group identifier <code>group_id</code>.</p>
  
  <p>Given a list of <code>atom_ids</code>, I'd like to be able to figure out which <code>group_id</code>, if any, that set of <code>atom_ids</code> belong to.  Solving this with just the <code>group_id</code> and <code>atom_id</code> columns was straightforward.</p>
  
  <p>Now I'm trying to generalize such that a 'group' is made up of not just a list of <code>atom_ids</code>, but other context as well.  In the example below, the list is ordered by including a <code>sequence</code> column, but conceptually other columns could be used instead, such as a <code>weight</code> column which gives each <code>atom_id</code> a [0,1] floating point value representing that atom's 'share' of the group.</p>
  
  <p>Below is most of a unit test demonstrating my issue.</p>
  
  <p>First, some setup:</p>
  
  <pre><code>def test_multi_column_grouping(self):
      class MultiColumnGroups(base.Base):
          __tablename__ = 'multi_groups'
  
          group_id = Column(Integer)
          atom_id = Column(Integer)
          sequence = Column(Integer)  # arbitrary 'other' column.  In this case, an integer, but it could be a float (e.g. weighting factor)
  
      base.Base.metadata.create_all(self.engine)
  
      # Insert 6 rows representing 2 different 'groups' of values
      vals = [
          # Group 1
          {'group_id': 1, 'atom_id': 1, 'sequence': 1},
          {'group_id': 1, 'atom_id': 2, 'sequence': 2},
          {'group_id': 1, 'atom_id': 3, 'sequence': 3},
          # Group 2
          {'group_id': 2, 'atom_id': 1, 'sequence': 3},
          {'group_id': 2, 'atom_id': 2, 'sequence': 2},
          {'group_id': 2, 'atom_id': 3, 'sequence': 1},
      ]
  
      self.session.bulk_save_objects(
          [MultiColumnGroups(**x) for x in vals])
      self.session.flush()
  
      self.assertEqual(6, len(self.session.query(MultiColumnGroups).all()))
  </code></pre>
  
  <p>Now, I want to query the above table to find which group a specific set of inputs belongs to.  I'm using a list of (named) tuples to represent the query parameters.</p>
  
  <pre><code>    from collections import namedtuple
      Entity = namedtuple('Entity', ['atom_id', 'sequence'])
      values_to_match = [
          # (atom_id, sequence)
          Entity(1, 3),
          Entity(2, 2),
          Entity(3, 1),
          ]
      # The above list _should_ match with `group_id == 2`
  </code></pre>
  
  <p>Raw SQL solution.  I'd <em>prefer</em> not to fall back on this, as a part of this exercise is to learn more SQLAlchemy.</p>
  
  <pre><code>    r = self.session.execute('''
          select group_id
          from multi_groups
          group by group_id
          having array_agg((atom_id, sequence)) = :query_tuples
          ''', {'query_tuples': values_to_match}).fetchone()
      print(r)  # &gt; (2,)
      self.assertEqual(2, r[0])
  </code></pre>
  
  <p>Here is the above raw-SQL solution converted fairly directly into a 
  broken SQLAlchemy query.  Running this produces a psycopg2 error: <code>(psycopg2.ProgrammingError) operator does not exist: record[] = integer[]</code>.  I believe that I need to cast the <code>array_agg</code> into an <code>int[]</code>? That would work so long as the grouping columns are all integers (which, if need be, is an acceptable limitation), but ideally this would work with mixed-type input tuples / table columns.</p>
  
  <pre><code>    from sqlalchemy import tuple_
      from sqlalchemy.dialects.postgresql import array_agg
  
      existing_group = self.session.query(MultiColumnGroups).\
          with_entities(MultiColumnGroups.group_id).\
          group_by(MultiColumnGroups.group_id).\
          having(array_agg(tuple_(MultiColumnGroups.atom_id, MultiColumnGroups.sequence)) == values_to_match).\
          one_or_none()
  
      self.assertIsNotNone(existing_group)
      print('|{}|'.format(existing_group))
  </code></pre>
  
  <p>Is the above <code>session.query()</code> close?  Have I blinded myself here, and am missing something super obvious that would solve this problem in some other way?</p>