Create SelectInterface::leftInnerJoin() and SelectInterface::leftOuterJoin() and make them fluent.

chx, we've been over this many times. The ability to return the assigned alias is mandatory. Full stop. The fields() method was added specifically to provide a fluent alternative to addField() so that addField() could still return the alias. This patch would break functionality.

If you want to make the select builder more fluent (making every method fluent is simply not on the table), suggest alternates for addExpression() or make the tagging and altering methods fluent, in a new issue.

I agree with chx, addField() and addExpression() are of the few methods that don't return $this.

I am deliberately being forward-looking here to complex alter statements and hopefully allowing Views to leverage the core query builder. Views asks for an automatic alias for virtually everything.

Input/output parameters are a nightmare to work with. They also break the entire concept of fluency because you have to initialize them first in a separate statement, pass them in, and then check them afterward. That's ridiculous, and far more work than it's worth.

There is absolutely zero reason to make addField() return $this. fields() was already added specifically to fill that need as well the ability to add multiple fields at once. I spent 2 hours talking to webchick about this already when we came to that conclusion. We've already solved this problem.

Making every single method on SelectQuery fluent is nonsensical, and a pointless religious adherence to fluency. I like fluent APIs, really, but to say that every method must be fluent is just silly.

Should addTag() in QueryAlterableInterface be fluent? Yeah, probably, since it doesn't return anything now. Let's do. What about hasTag()? Shall we pass in a variable that gets set to boolean TRUE or FALSE by reference just so that's fluent as well? Of course not, that would be moronic. As many methods should be fluent as possible while still having a clean API, and no more.

The correct solution, which is what we've already started doing with fields(), is to offer multiple mechanisms. That's nice and cheap and gives us the most flexibility. One thing I've considered is making join() fluent and requiring the use of innerJoin(), leftJoin(), and outerJoin() if you want the alias returned. That would fit with the way that fields() vs. addFields() now work. We can probably do something similar for addExpression().

Is this the reason we can't do the following (unlike db_insert, db_update, db_merge, and db_delete)?

$result = db_select('mytable', 'm')
  ->addField('m', 'field1')
  ->condition('field1', 1)
  ->execute();

And we have to do this instead:

$query = db_select('mytable', 'm');
$query->addField('m', 'field1');
$query->condition('field1', 1);
$result = $query->execute();

You can do this already:

$result = db_select('mytable', 'm')
  ->fields('m', array('field1'))
  ->condition('field1', 1)
  ->execute();

fields() is for adding multiple fields at once when you don't need the alias, and is therefore chainable. addField() is for adding one field at a time when you do care about the alias, and is therefore not chainable. The condition() method has always been chainable.

There is no problem here.

I guess we'll get back to this later.

Join absolutely should be fluent:

Ugly:

function mymodule_get_by_code($house_code, $langcode) {
  $query = db_select('node', 'n')
    ->fields('n', array('nid'))
    ->condition('n.language', $langcode);
  $query
    ->join('field_data_field_home_code', 'hc', 'hc.entity_id = n.nid and hc.language = n.language');
  $nids = $query
    ->condition('hc.field_home_code_value', $house_code)
    ->execute()
    ->fetchCol();
  return node_load(reset($nids));

Better:

  $nids = db_select('node', 'n')
    ->fields('n', array('nid'))
    ->condition('n.language', $langcode);
    ->join('field_data_field_home_code', 'hc', 'hc.entity_id = n.nid and hc.language = n.language');
    ->condition('hc.field_home_code_value', $house_code)
    ->execute()
    ->fetchCol();
  return node_load(reset($nids));

I can understand the reason that views wants auto-aliases, but they are about the only ones. Furthermore %alias can be used within the join itself. Use of a created alias further on can be done via optional reference arguments. However, the idea of only making join fluent has also its merits. But I think that it would be better to reserve addJoin for that and thus make join, innerJoin, leftJoin and rightJoin all fluent.

Summarizing: a +1 for this request with the following details:
- join, innerJoin, leftjoin and rightJoin become fluent without reference arguments.
- addJoin remains non-fluent and returns the (created) alias.

Adding an additional method as was done for fields() and addField() seems like a good idea if we need to have non-fluent variants. #10 sounds good as well.

We'd also want to clearly document which methods fall into which category. Would standard pattern for naming help, or be too much API shuffling for too little gain?

I also opened #1336726: Document which database methods can be chained (make sure it's correct).

Updating the IS and moving this to D9.

We could add new, fluent, methods in 8.x, moving back for now.

Updated the IS with what needs to be done.

I will do the reviewing for this.