CODEWITHSUNDEEP
phpmysqlcodeigniterjoin
nicolaib
nicolaib

Reputation: 667

Third join on NULL doesn't return values (Codeigniter)

I have a join statement that doesn't return the wanted values.

The reason is that the third table (cvi) in many cases won't contain any rows that match the fourth table (cmi). When no values are joined, the result does not show the wanted values from tables c and cv.

    /* Tables */
    $this->db->from($this->table_c.' c');
    $this->db->join($this->table_cv.' cv', 'cv.content_id = c.id', 'inner');
    $this->db->join($this->table_cvi.' cvi', 'cvi.version_id = cv.id AND cvi.meta_key = "M"');
    $this->db->join($this->table_cmi.' cmi', 'cmi.id = cvi.meta_value');

I have tried the outer joins but since the table cvi is in between the tables cv and cmi then there are no common values in the two tables.

+------------+
|        c   |
|            |
|      +-----|------+
|      |     |  cv  |
+------------+      | 
       |       +------------+
       |       |    |  cvi  | When this table is empty, the result is empty
       +-------|----+       | I want it to still show result of c and cv
               |      +------------+
               |      |     |  cmi |
               +------------+      |
                      |            |
                      |            |
                      +------------+

Here is an illustration of why there are no shared values. I'm therefore in the search for a way to make a condition that will only join cmi if cvi contains values. Otherwise only make an outer join on cvi and don't include the table cmi

Can you provide some ideas or solution?

EDIT Here are the tables for clarity: 

/* Table c */
+------+--------+
| id   | title  |
+------+--------+

/* Table cv */
+------+----------+
| id   | version  |
+------+----------+

/* Table cvi */
+------+------------+-----------+------------+
| id   | version_id | meta_key  | meta_value |
+------+------------+-----------+------------+
/* when meta_key is 'M' then the meta_value will contain the cmi.id which is used for the join (regard it as meta_id) */
/* When this table is empty there won't be data in `cmi` either. When it's empty the join removes the data result that should be present from table `c`. */

/* Table cmi */
+------+-----------+------------+
| id   | item_key  | item_value |
+------+-----------+------------+

Here is a result when there is data in the tables cv and cmi.

Array (
        [0] => stdClass Object
            (
                [id] => 5 /* This is c.id */
                [title] => Content title
                [version_id] => 8 /* This is cv.id */
                [version] => 0
                [meta_key] => M
                [meta_value] => 23 /* (This is the id of the item below, cmi.id) */
                [item_key] => KEY1
                [item_value] => Value
            )
)

Upvotes: 2

Views: 1942

Answers (1)

zuluk
zuluk

Reputation: 1577

In my opinion you have to take left joins:

/* Tables */
$this->db->from($this->table_c.' c');
$this->db->join($this->table_cv.' cv', 'cv.content_id = c.id', 'inner');
$this->db->join($this->table_cvi.' cvi', 'cvi.version_id = cv.id AND cvi.meta_key = "M"', 'left');
$this->db->join($this->table_cmi.' cmi', 'cmi.id = cvi.meta_value', 'left');

So you will get empty columns if the join conditions for cvi and cmi not create any result.

Edit after your comment: Maybe EXISTS helps:

$this->db->where("EXISTS(SELECT * FROM cmi)");

Codeigniter subquery exists

Upvotes: 0

Related Questions