Can't perform a UNION ALL MySQL query

[eluser]Ci beginner[/eluser]
Hi there,

I want to perform a MySQL query with UNION ALL using active records. However, I'm unable to do it. I follow an example in the forum in a post from somewhere but didn't work. This is my code:

Code:

function index()
    {
        $data['title'] = "Blog Test";
        $data['head'] = "Head";
        $data['footer'] = "Footer";
        
        $this->db->select('blog_comentarios.post_id, blog.id');
        $this->db->from('blog_comentarios, blog');
        $this->db->where('UNION ALL "
        SELECT * FROM `blog` ORDER BY `id` DESC
        UNION ALL
        SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"');
        
        $data['query'] = $this->db->get();

        $this->load->view('vistablog', $data);

    }

And the error is:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALL " SELECT * FROM `blog` ORDER BY `id` DESC UNION ALL SELECT COUNT(*) FROM ' at line 3

SELECT `blog_comentarios`.`post_id`, `blog`.`id` FROM (`blog_comentarios`, `blog`) WHERE `UNION` ALL " SELECT * FROM `blog` ORDER BY `id` DESC UNION ALL SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"

As you can see, is a syntax problem ("WHERE UNION ALL..."), but I don't really know how to make the UNION thing well. Could you give me an example?

Thank you very much in advance!

[eluser]Colin Williams[/eluser]
You need to tell the DB class to not protect identifiers by supplying a third param of TRUE.

Code:

$this->db->where('UNION ALL "
        SELECT * FROM `blog` ORDER BY `id` DESC
        UNION ALL
        SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"', NULL, TRUE);

[eluser]Ci beginner[/eluser]
[quote author="Colin Williams" date="1248684219"]You need to tell the DB class to not protect identifiers by supplying a third param of TRUE.

Code:

$this->db->where('UNION ALL "
        SELECT * FROM `blog` ORDER BY `id` DESC
        UNION ALL
        SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"', NULL, TRUE);

[/quote]

I can't try it now - I'm not @home. I trust you and it will probably works. Anyway, I don't fully understand why I need to use that. Would be nice if you can me a short explain about this, or if there's something about in the docs.

Thank you very much!

[eluser]Ci beginner[/eluser]
[quote author="Colin Williams" date="1248684219"]You need to tell the DB class to not protect identifiers by supplying a third param of TRUE.

Code:

$this->db->where('UNION ALL "
        SELECT * FROM `blog` ORDER BY `id` DESC
        UNION ALL
        SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"', NULL, TRUE);

[/quote]

Oops, bad news... I've tried the code:

Code:

$this->db->select('blog_comentarios.post_id, blog.id');
  $this->db->from('blog_comentarios, blog');
$this->db->where('UNION ALL "
  SELECT * FROM `blog` ORDER BY `id` DESC
  UNION ALL
  SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"', NULL, TRUE);  
  $data['query'] = $this->db->get();

And same error here:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALL " SELECT * FROM `blog` ORDER BY `id` DESC UNION ALL SELECT COUNT(*) FROM ' at line 3

SELECT `blog_comentarios`.`post_id`, `blog`.`id` FROM (`blog_comentarios`, `blog`) WHERE `UNION` ALL " SELECT * FROM `blog` ORDER BY `id` DESC UNION ALL SELECT COUNT(*) FROM blog_comentarios, blog WHERE blog_comentarios.post_id = blog.id"

What can I do? Thank you!

[eluser]kgill[/eluser]
Your SQL is the problem, read up on UNION ALL - you can't have mismatched columns, your 1st select is returning 2 columns, the 2nd is returning however many are in the blog table and the last select is returning 1. These are separate queries - why are you trying to combine them? It can be done if you ensure that each query has the same number of columns and returns the same types for each column, but any speed gains you get from making 1 trip from the web server to the DB are going to be lost in the amount of code you'll have to write to sort out what goes where when you get the result back.

[eluser]Ci beginner[/eluser]
[quote author="kgill" date="1248730530"]Your SQL is the problem, read up on UNION ALL - you can't have mismatched columns, your 1st select is returning 2 columns, the 2nd is returning however many are in the blog table and the last select is returning 1. These are separate queries - why are you trying to combine them? It can be done if you ensure that each query has the same number of columns and returns the same types for each column, but any speed gains you get from making 1 trip from the web server to the DB are going to be lost in the amount of code you'll have to write to sort out what goes where when you get the result back.[/quote]

Hey, thanks for your help. Actually I don't know how to make it with separate queries because I always have done this with UNION, but not in CI. I understand your explanation -- and I'm very grateful about it. However, if you can provide a working example for the UNION or without UNION (you know, separate queries) I'll understand how it works.

Thank you again!

[eluser]kgill[/eluser]

Actually I don’t know how to make it with separate queries

You're overthinking this:

Code:

$this->db->select(first query here);
$something = $this->db->get();

// repeat for each of the other queries
$this->db->select(2nd query here);
$something_else = $this->db->get();

// etc.

[eluser]Ci beginner[/eluser]
[quote author="kgill" date="1248733521"]

Actually I don’t know how to make it with separate queries

You're overthinking this:

Code:

$this->db->select(first query here);
$something = $this->db->get();

// repeat for each of the other queries
$this->db->select(2nd query here);
$something_else = $this->db->get();

// etc.

[/quote]

I can't do it... imagine this situation: we have a blog, with a query that gets the latest 5 posts. Now, we need to count the comments for every post, to write near the title "X comments". And the only way to do it is with UNION ("blog" for posts and "comentarios_blog" for comments), not with two queries, I think. But I'm unable to perform UNION thing because I don't know how to do in CI...

Thanks!

[eluser]bretticus[/eluser]
Not the only way to do it. Research sub queries at MySQL docs site. Also, the real benefit to active record is that you should be able to use any type of database w/o changing your model code. Just use a normal query since your sql is MySQL specific.


Simple ex. using query()

Code:

<?php

$query = $this->db->query("YOUR QUERY");

if ($query->num_rows() > 0)
{
   foreach ($query->result() as $row)
   {
      echo $row->title;
      echo $row->name;
      echo $row->body;
   }
}?>

[eluser]Ci beginner[/eluser]
[quote author="bretticus" date="1248738923"]Not the only way to do it. Research sub queries at MySQL docs site. Also, the real benefit to active record is that you should be able to use any type of database w/o changing your model code. Just use a normal query since your sql is MySQL specific.


Simple ex. using query()

Code:

<?php

$query = $this->db->query("YOUR QUERY");

if ($query->num_rows() > 0)
{
   foreach ($query->result() as $row)
   {
      echo $row->title;
      echo $row->name;
      echo $row->body;
   }
}?>

[/quote]

Okay, I think is the only way I can do the UNION. Thank you!