Welcome Guest, Not a member yet? Register   Sign In
Active record not generating required query [solved]
#1

[eluser]sudesh[/eluser]
Hi all,

Below is my code that i m using to get some records, but i m getting database error.

Code:
$this->db->select('store_prefix.*');
    $this->db->select('store.store_name');
    $this->db->select("(select GROUP_CONCAT(CAST(CONCAT(st.tot_start, ' - ', st.tot_end) AS CHAR ) ORDER BY st.tot_start SEPARATOR ', ')  from ".$this->db->dbprefix('store_tot')." st where st.tot_store = ".$this->db->dbprefix('store_prefix').".store_id) ranges", false);
    $this->db->from('store_prefix');
    $this->db->join('store_tot', 'store_prefix.store_id = store_tot.tot_store', 'left');
    $this->db->join('store', 'store_prefix.store_id = store.store_id', 'left');
    $this->db->group_by('store_prefix.prefix_id');

Its Produces query

Code:
SELECT bkv_store_prefix.*, bkv_store.store_name, (select GROUP_CONCAT(CAST(CONCAT(st.tot_start, ' - ', [b][u]bkv_[/u][/b]st.tot_end) AS CHAR ) ORDER BY st.tot_start SEPARATOR ', ') from bkv_store_tot st where st.tot_store = bkv_store_prefix.store_id) ranges FROM (bkv_store_prefix) LEFT JOIN bkv_store_tot ON bkv_store_prefix.store_id = bkv_store_tot.tot_store LEFT JOIN bkv_store ON bkv_store_prefix.store_id = bkv_store.store_id GROUP BY bkv_store_prefix.prefix_id

rather than

Code:
SELECT bkv_store_prefix.*, bkv_store.store_name, (select GROUP_CONCAT(CAST(CONCAT(st.tot_start, ' - ', st.tot_end) AS CHAR ) ORDER BY st.tot_start SEPARATOR ', ') from bkv_store_tot st where st.tot_store = bkv_store_prefix.store_id) ranges FROM (bkv_store_prefix) LEFT JOIN bkv_store_tot ON bkv_store_prefix.store_id = bkv_store_tot.tot_store LEFT JOIN bkv_store ON bkv_store_prefix.store_id = bkv_store.store_id GROUP BY bkv_store_prefix.prefix_id

Can't understand.

What wrong am i doing here.

Below is my table structure

CREATE TABLE IF NOT EXISTS `bkv_store_prefix` (
`prefix_id` int(11) NOT NULL auto_increment,
`prefix` varchar(100) NOT NULL,
`store_id` int(11) NOT NULL,
PRIMARY KEY (`prefix_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `bkv_store_tot` (
`tot_id` int(11) NOT NULL auto_increment,
`tot_start` int(11) NOT NULL,
`tot_end` int(11) NOT NULL,
`tot_used` int(11) NOT NULL default '0',
`tot_available` int(11) NOT NULL,
`tot_store` int(11) NOT NULL,
`tot_time` datetime NOT NULL,
PRIMARY KEY (`tot_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

Can somebody help.
#2

[eluser]Thorpe Obazee[/eluser]
wow. Could you put the sql statements in [ code] tags too? And kindly format it more legibly?

Anyway, Don't you need to add the dbprefix to those in CONCAT()?
#3

[eluser]tonanbarbarian[/eluser]
im sorta thinking that active record was never really designed to be used to handle subqueries
i would not use actiuve record in this case and just create the query by hand
#4

[eluser]Thorpe Obazee[/eluser]
sudesh, could you give the complete code?
#5

[eluser]sudesh[/eluser]
[quote author="bargainph" date="1245756680"]wow. Could you put the sql statements in [ code] tags too? And kindly format it more legibly?

Anyway, Don't you need to add the dbprefix to those in CONCAT()?[/quote]

Thanks , it worked

Now code looks below

Code:
$this->db->select('store_prefix.*');
    $this->db->select('store.store_name');
    $this->db->select("(select GROUP_CONCAT(CAST(CONCAT(".$this->db->dbprefix('store_tot').".tot_start,' - ',".$this->db->dbprefix('store_tot').".tot_end) AS CHAR ) ORDER BY ".$this->db->dbprefix('store_tot').".tot_start SEPARATOR ', ')  from ".$this->db->dbprefix('store_tot')." where ".$this->db->dbprefix('store_tot').".tot_store = ".$this->db->dbprefix('store_prefix').".store_id) ranges", false);
    $this->db->from('store_prefix');
    $this->db->join('store_tot', 'store_prefix.store_id = store_tot.tot_store', 'left');
    $this->db->join('store', 'store_prefix.store_id = store.store_id', 'left');
    $this->db->group_by('store_prefix.prefix_id');

Well i thought alias would work but it didn't as you said replace it with prefix done that

Where should i say it is solved???

Thanks all
#6

[eluser]Thorpe Obazee[/eluser]
edit the first post and add [solved] to the subject/title




Theme © iAndrew 2016 - Forum software by © MyBB