$post = $this->db->fetch_array_all( "SELECT * FROM `post` ORDER BY `id` DESC LIMIT 0, 10;" ); $type_1 = $this->db->fetch_array_all( "SELECT COUNT(*) FROM `smile` WHERE `post_id` = ".$this->db->quote_($this->id)." AND `enabled` = 1 AND `type_smile` = 1;"); $type_2 = $this->db->fetch_array_all( "SELECT COUNT(*) FROM `smile` WHERE `post_id` = ".$this->db->quote_($this->id)." AND `enabled` = 1 AND `type_smile` = 2;"); $type_3 = $this->db->fetch_array_all( "SELECT COUNT(*) FROM `smile` WHERE `post_id` = ".$this->db->quote_($this->id)." AND `enabled` = 1 AND `type_smile` = 3;");

Is it possible not to send 3 requests, but to execute 1 and no matter what the performance

Each post has 3 smiles:

  • :) it's good
  • -) this is normal
  • :( This is bad

All vote in the end then on the main page you will need to display the title of the post and 3 smiles with the number of votes for each smile, and the post is also a smile and it will need to be taken into account

As a result, we consider

Suppose on the page you need to display the last 10 topics, for each topic you need to display smiles and you get 30 requests !!!

How can you simplify?

Here it will look like

http://pixs.ru/showimage/testpng_4257106_9084930.png

    2 answers 2

     select sum(case `type_smile` when 1 then 1 else 0 end) as type1, sum(case `type_smile` when 2 then 1 else 0 end) as type2, sum(case `type_smile` when 3 then 1 else 0 end) as type3 FROM `smile` WHERE `post_id` = ".$this->db->quote_($this->id)." AND `enabled` = 1
    • And why not just: sum (if ( type_smile = 1, 1, 0)) as type1,? :) - Alex Kapustin
    • Because there is still type 2 and type 3 - knes
    • I'm talking about using IF instead of cumbersome CASE. And in general, I find fault :) - Alex Kapustin
     SELECT smyle.type_smile, COUNT(*) AS count_of_smiles FROM post INNER JOIN smile ON smile.post_id = post.id WHERE smile.enabled = 1 GROUP BY smile.type_smile ORDER BY count_of_smiles DESC