db_select+Left Join vs db_query

By FragSalat on 14 Feb 2012 at 09:48 UTC

Hey guys.

I hope anyone can help me.

I never created a working dynamic drupal query with a leftJoin but now I ask you for helping me to solve this problem.

I got the dynamic query which returns an empty array and i got an default mysql query string which i call with db_query.

The dynamic query (returns empty array)

$query = db_select('node', 'n');
$query->leftJoin('field_data_field_display_type', 'type', 'type.field_display_type_value = \'all\'');
$_gallery = $query->fields('n',array('nid'))
                      ->condition('n.type', 'gallery')
                      ->condition('n.nid', 'type.entity_id')
                      ->orderBy('changed', 'DESC')
                      ->range(0, 5)
                      ->execute();

The default query (returns correct results)

$sql = 'SELECT n.nid FROM node AS n '
          . 'LEFT JOIN field_data_field_display_type AS type ON type.field_display_type_value = \'step\' '
         . 'WHERE n.nid = type.entity_id AND n.type = \'gallery\' ORDER BY changed DESC LIMIT 0,5';
    
$_gallery = db_query($sql)->fetchAll();

Can you tell me what i make wrong.

Thanks in front of.

MfG Sick^

Comments

No one know something about

FragSalat commented 15 February 2012 at 14:56

No one know something about this topic?

what is the query that your

pelach commented 15 February 2012 at 15:12

what is the query that your "dynamic query" creates?

debug $_gallery and let us see the result.

As i already told. Dynamic

FragSalat commented 15 February 2012 at 17:51

As i already told.

Dynamic query returns array { }
Static query returns Data.

array(4) { [0]=> object(stdClass)#373 (3) { ["name"]=> string(5) "Stars" ["title"]=> string(27) "Another simple test gallery" ["nid"]=> string(3) "192" } [1]=> object(stdClass)#370 (3) { ["name"]=> string(5) "Stars" ["title"]=> string(11) "Gallery #11" ["nid"]=> string(3) "188" } [2]=> object(stdClass)#384 (3) { ["name"]=> string(5) "Stars" ["title"]=> string(11) "Gallery #10" ["nid"]=> string(3) "187" } [3]=> object(stdClass)#268 (3) { ["name"]=> string(5) "Stars" ["title"]=> string(12) "Paula Patton" ["nid"]=> string(3) "110" } }

The problem seems to be in the conditional

joecorall commented 16 February 2012 at 07:03

Hello,

I believe the problem is with one of your conditional clauses. Try:

$query = db_select('node', 'n');

you have two different values on the join in db_select() vs db_query(), so either:

$query->leftJoin('field_data_field_display_type', 't', "t.field_display_type_value = 'all'");

$query->leftJoin('field_data_field_display_type', 't', "t.field_display_type_value = 'step'");

and to execute the join query:

$_gallery = $query->fields('n', array('nid'))
  ->condition('n.type', 'gallery')
  ->where('n.nid = t.entity_id')
  ->orderBy('changed', 'DESC')
  ->range(0, 5)
  ->execute();

Using $query->condition('n.nid', 't.entity_id') would make the conditional in the query WHERE n.nid = 't.entity_id'

While $query->where('n.nid = t.entity_id') produces WHERE n.nid = t.entity_id

Thank you very much this

FragSalat commented 16 February 2012 at 09:25

Thank you very much this where solved my problem...

unbelievable that the 2nd condition cracked my query...

Here the correct dynamic query with a left Join.

$query = db_select('node', 'n');
$query->leftJoin('field_data_field_display_type', 'type', 'type.field_display_type_value = \'all\'');
$_gallery = $query->fields('n',array('nid'))
                       ->condition('n.type', 'gallery')
                       ->where('n.nid = type.entity_id')
                       ->orderBy('changed', 'DESC')
                       ->range(0, 5)
                       ->execute()
                       ->fetchAll();