Welcome Guest, Not a member yet? Register   Sign In
Activerecord - getting totalnumber of query when limit is used
#1

[eluser]WolfgangA[/eluser]
Hello,

using the activerecord class, i try to get the total number of records of a query and then only the records defined by limit.
So i need something like: "Record 20 - 40 of 1250" where i only fetch record 20-40.
Because the query joins more tables, i cannot use a simple count over one table.

I tried this:
Code:
$this->db->select('buchung.*, fz_klasse.klasse, fz_typ.typ, concat(vorname, " ", nachname) as mitarbeiter');
        $this->db->from('buchung');
        $this->db->join('fahrzeug', 'buchung.id_fz = fahrzeug.id', 'left');
        $this->db->join('fz_klasse', 'fz_klasse.id = fahrzeug.id_klasse', 'left');
        $this->db->join('fz_typ', 'fz_typ.id = fahrzeug.id_typ', 'left');
        $this->db->join('mitarbeiter', 'mitarbeiter.id = buchung.id_mitarbeiter', 'left');
        $this->db->where('buchung.id_fz', $id_fz);
        if ($like != '') { $this->db->like($like); }
        // get total number of records
        $query = $this->db->get();
        $ret['totalcount'] = $query->num_rows();

        // qurery again, but limit the resultset
        if ($limit  > 0) { $this->db->limit($limit, $start); }
        if ($orderby != '') { $this->db->orderby($orderby); }
        $query = $this->db->get();
        $ret['data'] = $query->result();

The problem is that after the first
Code:
$query = $this->db->get();
the query itself seems to be reset. So adding the limit and orderby clause fails.

The only way i have found would be to duplicate the entire query, but i would like to avoid that.
So is there any "nice" workaround possible?

Thanks in advance

Wolfgang
#2

[eluser]Michael Wales[/eluser]
Pagination Class
#3

[eluser]WolfgangA[/eluser]
Thanks for the link to the pagination class.
However this does not adress the issue i have.
My problem is: I need to get the total number of rows that match a (complex)query as well as only the resultset when using the 'limit' option.
The only way i see so far is to do 2 queries, which i would like to avoid.

Wolfgang
#4

[eluser]m9dfukc[/eluser]
yeah - i have the same problem. it works fine until i use the db->like() method for searching the fields.
-> then i got all querys and the db methods limit() and where() doesn't seem seen to work.
#5

[eluser]ELRafael[/eluser]
try to use $this->db->last_query()... maybe help you!
#6

[eluser]zdknudsen[/eluser]
Have a search for "SQL_CALC_FOUND_ROWS" on the forums :-) Failing that, although I doubt you will, google it.
#7

[eluser]barbazul[/eluser]
[quote author="WolfgangA" date="1186598375"]Thanks for the link to the pagination class.
However this does not adress the issue i have.
My problem is: I need to get the total number of rows that match a (complex)query as well as only the resultset when using the 'limit' option.
The only way i see so far is to do 2 queries, which i would like to avoid.

Wolfgang[/quote]

running two queries IS the way to go

Code:
$this->db->select("COUNT(id) AS total_num")
$this->db->from("table");
$this->db->where($conditions);
$first = $this->db->get();
$limit = $first->total_num;

$this->db->select("fields")
$this->db->from("table");
$this->db->where($conditions);
$this->db->limit($limit);
$second = $this->db->get();
Or something like that
#8

[eluser]zdknudsen[/eluser]
I'd still say SQL_CALC_FOUND_ROWS is to be prefered (if you have MySQL).
Code:
// just add SQL_CALC_FOUND_ROWS after SELECT and run whatever query you want
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE category = 'foo' LIMIT 10, 20

// then run this to find out how many rows have been found.
SELECT FOUND_ROWS()
However, I do think this is exclusive to MySQL.
#9

[eluser]ELRafael[/eluser]
[quote author="Zacharias" date="1189714033"]I'd still say SQL_CALC_FOUND_ROWS is to be prefered (if you have MySQL).
Code:
// just add SQL_CALC_FOUND_ROWS after SELECT and run whatever query you want
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE category = 'foo' LIMIT 10, 20

// then run this to find out how many rows have been found.
SELECT FOUND_ROWS()
However, I do think this is exclusive to MySQL.[/quote]

It's a solution, but you broke the idea of active record.....
#10

[eluser]zdknudsen[/eluser]
If he really wants to use AR he could just put "SQL_CALC_FOUND_ROWS" in the beginning of the first $this->db->select() statement. :-) But that defeats the purpose of using AR for multiple database-types.




Theme © iAndrew 2016 - Forum software by © MyBB