How to union 2 SQL-query in Codeigniter?

Question

Possible Duplicate:
UNION query with codeigniter's active record pattern

I have the following code:

$language_id=$this->get_language_id($language_code);
$english_id=$this->get_language_id('en');
$query="SELECT e.label_value, t.user_id, t.votes, t.approved, t.language_value FROM labels e left outer join labels t on e.label_value=t.label_value WHERE e.language=$english_id and t.language=$language_id and (t.approved=1 or t.user_id=$user_id) and e.label_value in (select distinct label_value from labels  WHERE language=$english_id order by label_value limit $start_index, 30) order by e.label_value, t.votes";
$query=$this->db->query($query);
$data=$query->result_array();

But I have got the following error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So, I need to do the folowoing part "select distinct label_value from labels WHERE language=$english_id order by label_value offset $start limit 30" in another query. Please, help me, how can I do it using CodeIgniter?

UPDATE:

There are is table labels

(label_value, language_value, language) - PK,
user_id,
timestamp,
approved,
votes

and I need to get all queries from this table (for example, it's name is t and e) with labels t.label_value, e.label_value (is exists), e.user_id, e.votes, e.timestamp where t.label_value=e.label_value(same label), t.language=45 (english language), e.language=24 (my language) and (e.user_id=121234 or e.approved=1). But I need all entries, and if (t.label_value!=e.label_value) I need to get this entry with NULL fields.

DaveRandom · Answer 1 · 2012-07-03T12:32:38.410

This is a limitation of MySQL and not PHP or CI. In order to get around it, you need to wrap your sub query in an aliased sub query so it becomes a derived table:

$language_id = $this->get_language_id($language_code);
$english_id = $this->get_language_id('en');
$query = "
  SELECT e.label_value, t.user_id, t.votes, t.approved, t.language_value
  FROM labels e
  LEFT OUTER JOIN labels t on e.label_value=t.label_value
  WHERE
    e.language = $english_id
    AND t.language = $language_id
    AND (t.approved = 1 OR t.user_id = $user_id)
    AND e.label_value IN (
      SELECT label_value
      FROM (
        SELECT DISTINCT label_value
        FROM labels
        WHERE language = $english_id
        ORDER BY label_value
        LIMIT $start_index, 30
      ) i
    )
  ORDER BY e.label_value, t.votes
";
$query = $this->db->query($query);
$data = $query->result_array();

I think that will work, let me know if it doesn't and I will take another look at it.

EDIT

I'm having a little difficulty working out exactly what you are trying to do, but I think it might be something more like this:

SELECT t.label_value, t.user_id, t.votes, t.approved, t.language_value
FROM (
  SELECT DISTINCT label_value
  FROM labels
  WHERE language = $english_id
) e
LEFT JOIN labels t ON e.label_value = t.label_value
WHERE
  t.language = $language_id
  AND (t.approved = 1 OR t.user_id = $user_id)
ORDER BY t.label_value, t.votes
LIMIT $start_index, 30

If this is still not correct, please show some example rows, and the result set you would like to retrieve from those rows.

Then the query is not correct. Please can you show the layout of your table and some sample data, and what results you hope to get from the table? I have a feeling it is `e.language = $english_id AND t.language = $language_id` that will be causing the problem, since you have joined the table onto itself and you're specifying that these values should be different, but it's hard to know where to go without seeing some of the data and what you hope to do with it. — DaveRandom, Jul 03 '12 at 11:42

How to union 2 SQL-query in Codeigniter?

1 Answers1