I am using Codeigniter query builder to generate my SQL query. On running the query through $this->db->get(), the result consists of null values in a PK column, which is incorrect (evidence is provided below).
On viewing the generated SQL query, through $this->db->get_compiled_select(), the SQL query is confirmed as properly created. Furthermore, I tested the SQL query in 2 different ways:
- By executing directly on MySQL. The result obtained is correct and does not contain null values in any PK columns.
- By running through Codeigniter again, but this time using
$this->db->query('SQL query string'). Again, th result obtained is correct and does not contain null values in any PK columns.
In addition to this, I logged the queries being sent to MySQL by turning on general query log as described in this resource. In all 3 cases (that is, running query through query builder class, running query directly through MySQL command line, and running query through codeigniter, but using $this->db->query()), the SQL query received by MySQL is the same.
Given the above evidence, am I right in arriving at the conclusion that this is a bug in Codeigniter Query Builder Class? Or am I missing some other source of error?
Given below are my table contents, and the source code:
Restaurant Table:
RestaurantID Name
1 Awesome Bar
2 Disco Deewaane
3 Decibel Disco
4 Basswaala Bar
Outlet Table (column RestaurantID is FK to Restaurant Table):
OutletID RestaurantID Name Address Phone Latitude Longitude
1 1 [->] Awesome Bar Roop Nagar 9837464231 0 0
2 1 [->] Aww Bar Prem Bhavan 8461232871 0 0
3 2 [->] Disco Deewaane Gali Number 420 8372313874 0 0
4 2 [->] Decibel Disc Excuse Me Please 9833346521 0 0
5 3 [->] Disc Dec Roop Nagar 8375643111 0 0
6 4 [->] Bass Down Low Prem Bhavan 9988843511 0 0
7 4 [->] Baby ka Bass Bar Gali Number 420 8883741234 0 0
8 4 [->] Basswaala Bar Excuse Me Please 9993741236,8843621439 0 0
OutletTable table (OutletID is FK to Outlet table):
TableID OutletID TableNumber Capacity Smoking Available
1 1 [->] 1 6 0 1
2 1 [->] 2 6 0 1
3 1 [->] 3 8 1 1
4 2 [->] 1 4 1 1
5 2 [->] 2 4 1 1
6 2 [->] 3 6 1 1
7 3 [->] 1 6 1 1
8 3 [->] 2 6 1 1
9 3 [->] 3 4 1 1
10 3 [->] 4 5 1 1
11 1 [->] z 10 1 0
Query in question:
SELECT *
FROM `Restaurant`
RIGHT JOIN `Outlet` ON `Restaurant`.`RestaurantID` = `Outlet`.`RestaurantID`
LEFT JOIN `OutletTable` ON `Outlet`.`OutletID` = `OutletTable`.`OutletID`
Correct result from query:
Result actually obtained when using Query Builder Class
Note the from index 11 onwards, the OutletID is not correctly reported and null is obtained (which is why it is shown as blank in print_r output below):
Array
(
[0] => stdClass Object
(
[RestaurantID] => 1
[Name] => Awesome Bar
[OutletID] => 1
[Address] => Roop Nagar
[Phone] => 9837464231
[Latitude] => 0
[Longitude] => 0
[TableID] => 1
[TableNumber] => 1
[Capacity] => 6
[Smoking] => 0
[Available] => 1
)
[1] => stdClass Object
(
[RestaurantID] => 1
[Name] => Awesome Bar
[OutletID] => 1
[Address] => Roop Nagar
[Phone] => 9837464231
[Latitude] => 0
[Longitude] => 0
[TableID] => 2
[TableNumber] => 2
[Capacity] => 6
[Smoking] => 0
[Available] => 1
)
[2] => stdClass Object
(
[RestaurantID] => 1
[Name] => Awesome Bar
[OutletID] => 1
[Address] => Roop Nagar
[Phone] => 9837464231
[Latitude] => 0
[Longitude] => 0
[TableID] => 3
[TableNumber] => 3
[Capacity] => 8
[Smoking] => 1
[Available] => 1
)
[3] => stdClass Object
(
[RestaurantID] => 1
[Name] => Awesome Bar
[OutletID] => 1
[Address] => Roop Nagar
[Phone] => 9837464231
[Latitude] => 0
[Longitude] => 0
[TableID] => 11
[TableNumber] => z
[Capacity] => 10
[Smoking] => 1
[Available] => 0
)
[4] => stdClass Object
(
[RestaurantID] => 1
[Name] => Aww Bar
[OutletID] => 2
[Address] => Prem Bhavan
[Phone] => 8461232871
[Latitude] => 0
[Longitude] => 0
[TableID] => 4
[TableNumber] => 1
[Capacity] => 4
[Smoking] => 1
[Available] => 1
)
[5] => stdClass Object
(
[RestaurantID] => 1
[Name] => Aww Bar
[OutletID] => 2
[Address] => Prem Bhavan
[Phone] => 8461232871
[Latitude] => 0
[Longitude] => 0
[TableID] => 5
[TableNumber] => 2
[Capacity] => 4
[Smoking] => 1
[Available] => 1
)
[6] => stdClass Object
(
[RestaurantID] => 1
[Name] => Aww Bar
[OutletID] => 2
[Address] => Prem Bhavan
[Phone] => 8461232871
[Latitude] => 0
[Longitude] => 0
[TableID] => 6
[TableNumber] => 3
[Capacity] => 6
[Smoking] => 1
[Available] => 1
)
[7] => stdClass Object
(
[RestaurantID] => 2
[Name] => Disco Deewaane
[OutletID] => 3
[Address] => Gali Number 420
[Phone] => 8372313874
[Latitude] => 0
[Longitude] => 0
[TableID] => 7
[TableNumber] => 1
[Capacity] => 6
[Smoking] => 1
[Available] => 1
)
[8] => stdClass Object
(
[RestaurantID] => 2
[Name] => Disco Deewaane
[OutletID] => 3
[Address] => Gali Number 420
[Phone] => 8372313874
[Latitude] => 0
[Longitude] => 0
[TableID] => 8
[TableNumber] => 2
[Capacity] => 6
[Smoking] => 1
[Available] => 1
)
[9] => stdClass Object
(
[RestaurantID] => 2
[Name] => Disco Deewaane
[OutletID] => 3
[Address] => Gali Number 420
[Phone] => 8372313874
[Latitude] => 0
[Longitude] => 0
[TableID] => 9
[TableNumber] => 3
[Capacity] => 4
[Smoking] => 1
[Available] => 1
)
[10] => stdClass Object
(
[RestaurantID] => 2
[Name] => Disco Deewaane
[OutletID] => 3
[Address] => Gali Number 420
[Phone] => 8372313874
[Latitude] => 0
[Longitude] => 0
[TableID] => 10
[TableNumber] => 4
[Capacity] => 5
[Smoking] => 1
[Available] => 1
)
[11] => stdClass Object
(
[RestaurantID] => 2
[Name] => Decibel Disc
[OutletID] =>
[Address] => Excuse Me Please
[Phone] => 9833346521
[Latitude] => 0
[Longitude] => 0
[TableID] =>
[TableNumber] =>
[Capacity] =>
[Smoking] =>
[Available] =>
)
[12] => stdClass Object
(
[RestaurantID] => 3
[Name] => Disc Dec
[OutletID] =>
[Address] => Roop Nagar
[Phone] => 8375643111
[Latitude] => 0
[Longitude] => 0
[TableID] =>
[TableNumber] =>
[Capacity] =>
[Smoking] =>
[Available] =>
)
[13] => stdClass Object
(
[RestaurantID] => 4
[Name] => Bass Down Low
[OutletID] =>
[Address] => Prem Bhavan
[Phone] => 9988843511
[Latitude] => 0
[Longitude] => 0
[TableID] =>
[TableNumber] =>
[Capacity] =>
[Smoking] =>
[Available] =>
)
[14] => stdClass Object
(
[RestaurantID] => 4
[Name] => Baby ka Bass Bar
[OutletID] =>
[Address] => Gali Number 420
[Phone] => 8883741234
[Latitude] => 0
[Longitude] => 0
[TableID] =>
[TableNumber] =>
[Capacity] =>
[Smoking] =>
[Available] =>
)
[15] => stdClass Object
(
[RestaurantID] => 4
[Name] => Basswaala Bar
[OutletID] =>
[Address] => Excuse Me Please
[Phone] => 9993741236,8843621439
[Latitude] => 0
[Longitude] => 0
[TableID] =>
[TableNumber] =>
[Capacity] =>
[Smoking] =>
[Available] =>
)
)
