On Tue, Nov 25, 2008 at 10:22 AM, Peter Rabbitson <[EMAIL PROTECTED]> wrote: > Noel Burton-Krahn wrote: >> >> The problem is that DBIx has the "smarts" to prefix column names in >> the select clause, like "select me.id, cds.id" but not in the where >> clause "where id=?". > > How does DBIC know if the user meant 'where me.id = ?' and not 'where > cds.id = ?'. Blindly prefixing stuff with 'me' is plain wrong. >
Consider the following search from a schema where person and address both have a column named 'id': $schema->resultset('Person')->search({ id => $person_id}, { prefetch => [ 'address' ] }); This is a reasonable query: load a person with address by the person's id. The 'id' column is unambiguous in the search call. However, DBIx generates ambiguous SQL: DBIx::Class::ResultSet::count(): DBI Exception: DBD::mysql::st execute failed: Column 'id' in where clause is ambiguous [for Statement "SELECT COUNT( * ) FROM person me LEFT JOIN address address ON ( address.person_id = me.id ) WHERE ( id = ? )" with ParamValues: 0='1'] at ./t/dbix_unique_prefix_where.t line 116 The simple way to fix it is to prefix "id" as "me.id". I'd argue DBIx should prefix all unqualified column names in the first argument to search(). Asking users to rely on DBIx's current internal strategy of using "me" as a prefix is not safe. What if "me" changes to "this" in a future release? What if I make a table named "me"? ~Noel _______________________________________________ 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