Otimizando consultas SQL

Estou desenvolvendo um sistema de gerenciamento de conteúdo no momento, e queria ouvir seus pensamentos sobre o seguinte:

Eu tenho uma tabela, uma page . Vamos assumir que parece isso

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

Além disso, eu tenho uma tabela de page_option (para que eu possa armazenar opções relacionadas à página, mas não quero ter uma lista finita de opções – os módulos podem adicionar suas próprias opções a uma página, se necessário).

A tabela page_option pode ser assim:

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

Agora, para recuperar um object de página, faço o seguinte usando a class Active Record (este foi pseudo codificado para esta questão):

 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(); } 

O que eu quero saber, existe uma maneira de fazer isso em uma consulta, de modo que as chaves da opção se tornem colunas virtuais na minha seleção, então eu obtive:

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

Nos meus resultados, em vez de fazer uma consulta separada para cada linha. E se houvesse 10.000? Etc.

Muito obrigado antecipadamente!

Usando o modelo EAV ( Entidade-Atributo-Valor ), você sempre terá que lidar com esse tipo de problemas. Eles também não são muito eficientes devido à complexidade das consultas (o giro é necessário na maioria deles).

 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 

Por exemplo, dada esta tabela:

 | 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 | 

Você terá a seguinte saída:

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

Fiddle aqui .

Então, apenas junte-se à tabela de page e é isso 🙂 Eu omitei essa parte para focar a consulta no próprio agrupamento.

Se você pode adicionar campos virtuais com a class activerecord, você pode fazer algo semelhante:

  $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)"); 

Não será ótimo …

Se a option_key for uniqe por page_id (você não tem dois ou mais resources com page_id==1 ), você pode fazer:

 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 

Você pode executar esta consulta SQL e colocar seu resultado em $result . Depois de fazer todos os itens de $result :

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

Você pode fazer isso com um foreach ou você também pode usar o array_walk .

Depois disso, você possui uma matriz associativa com opções em $result[$i]["options"] :

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

Espero que seja o que você deseja.