UNION support is missing in database API

Thank you for quick feedback.

My main issue with the above query is the db_placeholders($ignore_response_codes, 'int'). I have no idea how could looks like in a static fashion. db_placeholders() is gone and I do not know how to convert to D7... If you could give me an example it would be very helpful.

I'm still not sure what the difference is between a static and dynamic query and when I should use on or the other... I was told on IRC complicated querys need to use db_select()... so I expect I have nearly only complicated querys next to me for the reason of db_placeholders() :-(

I'm sorry about the UNION implementation... as I'm still trying to upgrade a module... I do not have the background to develop the core database API stuff :-(. But having no UNION support would be critical for linkchecker module. I may never get a D7 version out if this is not possible. :-(((

Oh my goodness... Thank you *very* much. I overcomplicated the most query's :-(. This magic was my problem and this is not documented or I missed it... we should add such an "IN" example to the DB API doc pages (http://drupal.org/node/310086)... not only the "easy" statements...

Crell: Is it possible that extend('PagerDefault')->extend('TableSort') is required to build a pager? I've found this in core very often and there is *no* pager in core that is not build this way. Looks like I need to move all Pager with/o TableSort to db_select() !?

But how can I use PagerDefault without db_select()? With db_select I cannot use UNION... :-(

@Crell: The only thing I can come out is the below... but it's not working at all and misses $subquery3 and $subquery4. Are you able to work on the UNION stuff, please? I can test, but cannot develop the driver. I really hope that such a critical thing can get in after code freeze if it breaks modules working in past!? "nicely" is something that we can nail out later... but "working" is required.

  $ignore_response_codes = preg_split('/(\r\n?|\n)/', variable_get('linkchecker_ignore_response_codes', "200\n302\n304\n401\n403"));

  $subquery4 = db_select('linkchecker_node', 'ln')
    ->distinct()
    ->fields('ln', array('lid'));

  $subquery3 = db_select('linkchecker_comment', 'lc')
    ->distinct()
    ->fields('lc', array('lid'));

  $subquery2 = db_select('linkchecker_box', 'lb')
    ->distinct()
    ->fields('lb', array('lid'));

  $subquery1 = db_select($subquery2, 'q1')->fields('q1', array('lid'));

  $query = db_select('linkchecker_link', 'll')->extend('PagerDefault')->extend('TableSort');
  $query->innerJoin($subquery1, 'q2', 'q2.lid = ll.lid');
  $query->fields('ll');
  $query->condition('ll.last_checked', 0, '<>');
  $query->condition('ll.status', 1);
  $query->condition('ll.code', $ignore_response_codes, 'NOT IN');
  $test = $query->execute();

  krumo($test);

This gives me:

SELECT ll.* FROM linkchecker_link ll
INNER JOIN (
  SELECT q1.lid AS lid FROM (
    SELECT DISTINCT lb.lid AS lid FROM linkchecker_box lb
  ) q1
) q2 ON q2.lid = ll.lid
WHERE (ll.last_checked <> :db_condition_placeholder_1) AND (ll.status = :db_condition_placeholder_2) AND (ll.code NOT IN (:db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5, :db_condition_placeholder_6, :db_condition_placeholder_7)) LIMIT 0, 10

If you have an idea how to build a pager query without db_select() - PLEASE shed some light on me. I'm fully lost here and I do not like to discontinue the linkchecker module only for a core DB API that is missing a small feature. :-(

*GREAT* - I will give this a try in a few hours. What is about "UNION ALL"?

I cannot test. Latest core code has a broken install process and the next oldest version I have is ~8 days old and the patch does not apply to this old code :-(.

I used my 1 week old core version with the latest database folder to install and apply the patch. By this I was able to get this working as expected and changed in the #9 example the line

$subquery1 = db_select($subquery2, 'q1')->fields('q1', array('lid'));

to

$subquery1 = db_select($subquery2->union($subquery3)->union($subquery4), 'q1')->fields('q1', array('lid'));

what resulted in the expected SQL statement:

SELECT ll.* FROM linkchecker_link ll 
INNER JOIN (
  SELECT q1.lid AS lid FROM (
    SELECT DISTINCT lb.lid AS lid FROM linkchecker_box lb 
    UNION
    SELECT DISTINCT lc.lid AS lid FROM linkchecker_comment lc
    UNION
    SELECT DISTINCT ln.lid AS lid FROM linkchecker_node ln
  ) q1
) q2 ON q2.lid = ll.lid
WHERE (ll.last_checked <> :db_condition_placeholder_0) AND (ll.status = :db_condition_placeholder_1) AND (ll.code NOT IN (:db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5, :db_condition_placeholder_6)) LIMIT 0, 10

This patch solves my UNION only issue, but I believe we should have UNION ALL, too.

Codewise it looks like you have missed something the the __clone(). I'm also not sure if it makes more sense to have something like ->union($queryObject, 'ALL') as it may not duplicate so much code and looks like the condition logic.

@Crell: There is a link in #17 that explain the difference between UNION and UNION ALL. UNION ALL does not merge the same entries into one. If UNION only is used the *same* rows from different tables are merged into one row. This is not the case with UNION ALL. You see in the latest test an update for this...

or with other words:

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

I'm not sure what UNION DISTINCT does... maybe not DB independed - never heard about it. Sounds like normal UNION.

t('UNION ALL correctly preserved duplicates.') sounds wrong... it need to be UNION and does NOT preserve duplicates... I believe you have flipped t('UNION correctly discarded duplicates.') with t('UNION ALL correctly preserved duplicates.')

UNION and UNION DISTINCT seems to be the same, see http://dev.mysql.com/doc/refman/5.1/en/union.html.

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

You can mix UNION ALL and UNION DISTINCT in the same query. Mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.

Sounds like UNION DISTINCT is defined in the Standards and UNION not... maybe we should better go with the Standard :-)

Why are we not merge UNION with UNION ALL and UNION DISTINCT by using ->union($selectQuery, 'ALL'), ->union($selectQuery, 'DISTINCT') and so on. This would also fix the issue Crell pointed out.

I've found a few code style issues...

1. We may need to remove this from the inline documentation as it's only correct for UNION and UNION DISTINCT.

Duplicate columns will
+   * be discarded.

2. "An array of additional Select queries." documentation also have some good space we can use until the 80 letters limit per line is reached :-). Currently max 50 is used.

3. A few trailing space issues need to be fixed. One is above "// Ensure we only get 2 records." others in the tests and so on.

4. "explicity" -> explicitly

Code wise it looks good... I will try to test.

Patch attached does only change the documentation as written in #31.

I've tested UNION, UNION ALL, UNION DISTINCT with linkchecker and all works as expected (statements have been verified with latest core / devel query output).

After a quick search around... I'm currently not for adding distinct by default. It looks not very well supportet. Sqlite, mssql may not understand it. No idea what about oracle. Does pdo themself help us here?

I like the validation idea, too. We may also think about automatically changing UNION DISTINCT into UNION only if a developer use it. If we know for sure it's the same like UNION and someone uses it, but it's not working in all DB servers we should care about this... I'm not sure what evangelism rule apply here for Drupal, but it shouldn't be a developers decision to make code incompatible.

The sqlite docs are only talking about UNION and UNION ALL. I have no sqlite with me and cannot verify if DISTINCT nevertheless works... MsSQL2005 documentation also does not have anything about UNION DISTINCT... standard or not... we better go with UNION only for UNION DISTINCT - only to be save and for compatibility reasons. I cannot say anything about INTERSECT, MINUS and EXCEPT, but if this is only available on Oracle I'm not sure if we should support it!???

I like APIs that work flawless and 100% DB independent...

UNION MINUS (Oracle) is also such a candidate...

I hope Crell can comment on the evangelism?

Updated patch. I hope we are done now.

Updated patch.

Updated patch.

This code is RTBC. Do we need to get this in *before* code freeze? It cannot wait for D8...

THX

I wonder how it's possible to make

• MySQL: SUBSTR(), SUBSTRING()
• Oracle: SUBSTR()
• SQL Server: SUBSTRING()

and

• MySQL: TRIM(), RTRIM(), LTRIM()
• Oracle: RTRIM(), LTRIM()
• SQL Server: RTRIM(), LTRIM()

database independent without switch statements. We may need to fold TRIM() to LTRIM(RTRIM(' string ')) and I'm not sure what we need to do with SUBSTRING. But this is OT here... Give me a hint if we should fix these issues in a two follow up issues :-)

@Dries: THX!!!

@Crell: Can you point us to the thread, please? I cannot find it...