[Dbix-class] ResultSet chaining help

[Jesse Sheidlower]

A few days ago, I asked a question on #dbix_class about
forcing a particular join to be an inner join (as was
specified in the schema definition), instead of a left join
(as happened in the generated query); my app had started to
fail because of a change in DBIC, but apparently my technique
was flawed in the first place. I got some helpful suggestions
from ribasushi and mst, but I'd like to ask again more
formally here, because in order to rewrite what is currently
some very complicated code, I want to make sure I fully
understand it going in.

The main difficulty now is that I use a number of subroutines
to build data structures from which my search is
constructed. I've adopted various tricks to construct things
properly, and while I'm sure that there are better tricks (or
that these tricks aren't even necessary), I'm not sure what
these are. In particular, constructing the join clause has
proven very difficult.

My basic table structure is like this (excuse the poor
diagramming, and these are has-many's going down and out):

     Region >- Source -< Subject
                 |
                 ^
      Title >- Part -< Author
                 |
                 ^
              Quotation -< Quotation_lookup (from Sphinx)
                 |
                 ^
    Region >- cwGroup -< Subject


There are more details, but this will do.

Most searches need to return an RS of cwGroups. Some searches
need to return an RS of sources. A cwGroup RS will _always_
need to prefetch Part. A Source RS only needs Source.

The general use case here is that I have a web form that has
fields representing columns in various of these tables.  Which
tables are joined will depend on what's entered. 

In the most common and simplest case, someone will search
based on a field in the cwGroup table, that table can be
queried, joined through to the Part table (the results are
also always sorted by a field in the Part table), and that's
it.

In a more complicated, but by no means uncommon, case, a
search will be based on values in the Source table, perhaps
even a source-subject thing, as well as things in the cwGroup
table, and we'll have to join all the way through from
source-subject down through cwGroup (even though there might
not be anything being searched in Part or Quotation).

The Quotation_lookup thing is a temporary table generated from
the results of a query in the Sphinx search engine; if someone
wants to do a full-text search of the Quotation table, I do it
in Sphinx, return a list of Quotation ID's, and put these into
Quotation_lookup.

In this case, and in the case of Author, Title, Subject, or
Region searches, these all need to be inner joins, not left
joins--I want to get only those results where there _is_ a
matching row in the quotation-lookup, author, etc. table--if
it doesn't match, I don't want a result returned. Obviously.
I had previously declared these with a join_type of 'INNER' in
my schema declaration, but I now get the impression that I
just have to create the query differently.

What I've been doing is keeping track of what tables are used
for a query, and then having a _get_cwGroup_join or
_get_source_join routine that has a big if-else table going
through various possibilities for which tables need to be
joined in, and return a data structure for the join. 

Finally--the database is large enough that I do want to
execute these as single joined queries, not multiple queries.

Thanks for reading this far.

Jesse Sheidlower