Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-14639

2 same tables in sql_build_query

    XMLWordPrintable

Details

    Description

      when you set 2 aliases to one table in an sql_array, sql_build_query($array) ignores the first alias and only includes the 2nd alias in the sql.
      example :

      $sql_array = array (
      	'SELECT' => ' a.id, a.game_id, a.title, a.points, a.description, a.icon, a.factionid,
      		r1.rel_value as criteria_id,
      		r2.rel_value as rewards_item_id,
      		c.description as criteria, c.orderindex as criteriaorder, c.max as criteriamax,
      		w.description as rewards, w.orderindex as rewardorder, w.max as rewardmax',
      	'FROM' => array (
      		ACHIEVEMENT_TABLE => 'a',
      		BB_RELATIONS_TABLE => 'r1',
      		BB_RELATIONS_TABLE => 'r2',
      		ACHIEVEMENT_CRITERIA_TABLE => 'c',
      		ACHIEVEMENT_REWARDS_TABLE => 'w',
      		),
      	'WHERE' =>  'a.id = ' . (int) $this->id .
      		" AND a.game_id = '". $this->game_id .
      		"' AND r1.attribute_id = 'ACH' AND r1.rel_attr_id = 'CRI' AND r1.att_value = a.id   " .
      		" AND r2.attribute_id = 'ACH' AND r2.rel_attr_id = 'REW' AND r2.att_value = a.id " .
      		" AND c.criteria_id = r1.rel_value " .
      		" AND c.rewards_item_id = r2.rel_value "
      );
       
      $sql = $db->sql_build_query('SELECT', $sql_array);
      

      this gets translated to

      SELECT a.id,
             a.game_id,
             a.title,
             a.points,
             a.description,
             a.icon,
             a.factionid,
             r1.rel_value  AS criteria_id,
             r2.rel_value  AS rewards_item_id,
             c.description AS criteria,
             c.orderindex  AS criteriaorder,
             c.max         AS criteriamax,
             w.description AS rewards,
             w.orderindex  AS rewardorder,
             w.max         AS rewardmax
      FROM   (phpbb_bb_achievement a
              CROSS JOIN phpbb_bb_relations_table r2
              CROSS JOIN phpbb_bb_achievement_criteria c
              CROSS JOIN phpbb_bb_achievement_rewards w)
      WHERE  a.id = 4860
         AND a.game_id = 'wow'
         AND r1.attribute_id = 'ACH'
         AND r1.rel_attr_id = 'CRI'
         AND r1.att_value = a.id
         AND r2.attribute_id = 'ACH'
         AND r2.rel_attr_id = 'REW'
         AND r2.att_value = a.id
         AND c.criteria_id = r1.rel_value
         AND c.rewards_item_id = r2.rel_value 
      

      The "r1" table is missing from the SQL.

      this is a bug.

      Attachments

        Activity

          People

            Marc Marc [X] (Inactive)
            Sajaki Sajaki [X] (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: