CODEWITHSUNDEEP
phpmysqlcodeigniteroptimization
Tom Hallam
Tom Hallam

Reputation: 1950

Optimising SQL Queries

I'm developing a content management system at the moment, and I wanted to hear your thoughts on the following:

I have one table, page. Let's assume it looks like this

ID | Title | Content
1  | Test  | This is a test

As well as this, I have a page_option table (so I can store options relating to the page, but I don't want to have a finite list of options - modules could add their own options to a page if required.)

The page_option table could look like this:

page_id | option_key  | option_value
1       | background  | red
1       | module1_key | chicken

Now to retrieve a page object, I do the following using the Active Record class (this was pseudo coded for this question):

function get_by_id($page_id) {

 $this->db->where('id', $page_id);
 $page_object = $this->db->get('page');

 if($page_object->num_rows() > 0) {

   $page = $page_object->row();
   $this->db->where('page_id', $page_id);
   $options_object = $this->db->get('option');

   if($options_object->num_rows() > 0) {
     $page->options = $options_object->result();
   }

   return $page;

 }

 return $page_object->row();

}

What I want to know, is there a way to do this in one query, so that the option keys become virtual columns in my select, so I'd get:

ID | Title | Content        | background | module1_key
1  | Test  | This is a test | red        | chicken

In my results, rather than doing a seperate query for every row. What if there were 10,000? Etc.

Many thanks in advance!

Upvotes: 2

Views: 131

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

Using the EAV (Entity-Attribute-Value) model you will always have to cope with these kind of issues. They're also not ver efficient due to the complexity of the queries (pivoting is required in most of them).

SELECT page_id,
  MAX(CASE WHEN option_key = 'background' THEN option_value END) background,
  MAX(CASE WHEN option_key = 'module1_key' THEN option_value END) module1_key,
  MAX(CASE WHEN option_key = 'module2_key' THEN option_value END) module2_key
FROM page_option
GROUP BY page_id

For example, given this table:

| PAGE_ID |  OPTION_KEY | OPTION_VALUE |
|---------|-------------|--------------|
|       1 |  background |          red |
|       1 | module1_key |      chicken |
|       2 | module1_key |         duck |
|       3 | module1_key |          cow |
|       4 |  background |         blue |
|       4 | module2_key |        alien |
|       4 | module1_key |      chicken |

You will the following output:

| PAGE_ID | BACKGROUND | MODULE1_KEY | MODULE2_KEY |
|---------|------------|-------------|-------------|
|       1 |        red |     chicken |      (null) |
|       2 |     (null) |        duck |      (null) |
|       3 |     (null) |         cow |      (null) |
|       4 |       blue |     chicken |       alien |

Fiddle here.

Then just join with the page table and that's it :) I've omitted that part in order to focus the query in the grouping itself.

Upvotes: 1

uzsolt
uzsolt

Reputation: 6037

If option_key is uniqe per page_id (you don't have two or more background with page_id==1) you can do:

SELECT page.page_id, page.title, page.content,
  GROUP_CONCAT(option_key SEPARATOR '@') AS option_keys,
  GROUP_CONCAT(option_value SEPARATOR '@') as option_values,
FROM page
LEFT JOIN page_option ON page_option.page_id=page.page_id
WHERE page.page_id=USER_SPECIFIED_ID

You can execute this SQL-query and put its result into $result. After you should do every item of $result:

$result[$i]["options"] = array_combine(
        explode("@",$result[$i]["option_keys"]),
        explode("@",$result[$i]["option_values"])
);

You can do it with a foreach or you can use array_walk too.

After these you've an associative array with options in $result[$i]["options"]:

{
  "background" => "red",
  "module_key1"=> "chicken"
}

I hope it's what do you want.

Upvotes: 0

Lajos Veres
Lajos Veres

Reputation: 13725

If you can add virtual fields with the activerecord class you can do something similar:

 $this->db->add_field("(select group_concat(concat(option_key,':',option_value)  SEPARATOR ' ') from page_option where page_id=$page_id group by page_id)");

It wont be optimal...

Upvotes: 0

Related Questions