Complex SQL -> converting to active records

Complex SQL -> converting to active records - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Complex SQL -> converting to active records (/showthread.php?tid=25391)

Complex SQL -> converting to active records - El Forum - 12-10-2009

[eluser]JamesTaylor[/eluser]
I have the following sql function in my model which works as expected in its current state, but i would like convert it to a CI active records statement

Code:
$sql = "SELECT ClubID, Name, Lat, Lng, Address1, Address2, Address3, County, PostCode, TelArea, TelNo, Web, Email, Review, ( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) ) AS distance FROM golfcourses HAVING distance < $Dist ORDER BY distance LIMIT 0 , 20";

$query = $this->db->query($sql);

I would like to add some pagination to the results so am wanting to convert the above sql into a CI active records statement.

I'm not sure but does the CI pagination only work with active records? I've had it work on other elements using active record without any problems but i can't get it to work on the above traditional sql statement?

help converting the sql would be much appreciated anyway, i'll keeep trying in the mean time!

Thanks

James

Complex SQL -> converting to active records - El Forum - 12-10-2009

[eluser]jedd[/eluser]
[quote author="JamesTaylor" date="1260480656"]
I would like to add some pagination to the results so am wanting to convert the above sql into a CI active records statement.

I'm not sure but does the CI pagination only work with active records?
[/quote]

No - you can easily just put your LIMIT and offset values in to your usual (non-AR) queries.

What you may want to do is use binding or manually escape your variables in your queries, at least.

Complex SQL -> converting to active records - El Forum - 12-10-2009

[eluser]JamesTaylor[/eluser]
Thanks Jedd, a bit more playing around and it appeared that my problem was being caused by a simple ',' being included in the sql!

i was trying

Code:
...ORDER BY distance LIMIT $limitValue, $OffsetValue

removing the ',' to make

Code:
...ORDER BY distance LIMIT $limitValue $OffsetValue

has solved my problem...

...although now the pagination works it has thrown up another problem! Always the case!

The intial search is triggered by a user inputing a postcode and distance into a form and hitting submit which passes the values through to the controller and onto the model etc... but now with pagination involved when i try to move to the 2nd page of results the controller doesn't recieve the values from the form (postcode and distance) which are needed to run the model and return the 2nd page of results??

What would be the advised way to make this work as although its the 1st time i have encountered such an issues in my short coding life i expect it isn't very uncommon... i'm thinking about storing the values in a session maybe but i figure that may be overkill??

Your mentioning of escaping the variables kinda raises an issue i was planning on looking into further quite soon across the entire application i am developing... my current understanding is that CI automatically escapes when using ActiveRecord but i would have to do it manually if i use traditional sql statments... is that correct?? ...as i plan to come on to this shortly i'll leave my questions at that for now and concentrate on trying to get the pagination / search results to function as intended!

Thanks again

james

Complex SQL -> converting to active records - El Forum - 12-10-2009

[eluser]Phil Sturgeon[/eluser]

Code:
$this->db->select('ClubID, Name, Lat, Lng, Address1, Address2, Address3, County, PostCode, TelArea, TelNo, Web, Email, Review')

    ->select('( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) ) AS distance', FALSE)

    ->having('distance <', $Dist)

    ->order_by('distance')

    ->limit(20);

$query = $this->db->get('golfcourses');

Or something along those lines.

Complex SQL -> converting to active records - El Forum - 12-10-2009

[eluser]JamesTaylor[/eluser]
Thanks Phil, got it working with ActiveRecords using the following... so i now have working in both fashions which is comforting and confidence building!

Code:
$this->db->select('ClubID, Name, Lat, Lng, Address1, Address2, Address3, County, PostCode, TelArea, TelNo, Web, Email, Review')

         ->select('"( 3959 * acos( cos( radians($Lat) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians($Lng) ) + sin( radians($Lat) ) * sin( radians( Lat ) ) ) )" AS distance');

$this->db->having('distance < ' .$Dist);

$this->db->order_by('distance');

$this->db->limit($LimitValue, $OffsetValue);

$query = $this->db->get('golfcourses');

a couple of things i notice with your sample...

you don't declare '$this->db' again after the inital '$this->db->select' statement i didn't realise it could be done this way, is it common / good / better practice to implement your style than the repetition that i use?

I note that in the code i have supplied which works as it is shown, if i have

Code:
$this->db->select(........

$this->db->select(........

$this->db->having(........

it throws up an error when it tries to execute, but when it is

Code:
$this->db->select(........

         ->select(........

$this->db->having(........

It works??

The other thing i don't quite follow is the 'FALSE' you have included at the end of the 2nd ->select() line. What is its purpose?

Thanks again for the reply!