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 _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk