• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Complex SQL -> converting to active records

#1
[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

#2
[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.

#3
[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

#4
[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.

#5
[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!


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.