Returning paginated results from a complex SQL query

Hi all, just starting out a new project in the property listing sector. I'm going round in circles now over how best to structure my property listing search, and I could be missing something really obvious but here goes.

Basically, I have a simple Listing Model which lists all properties if no search input has occurred. This works as expected with pagination, and can be called from my controller as below... I also have a map which plots the property listings using the lat and lng stored with the property.

PHP Code:

$listings = $this->ListingModel->paginate(20);
 $pager = $this->ListingModel->pager;
$data = [
'listings' => $listings,
'pager' => $pager,
]; 


This is then sent to the view, works great.. so far so simple.


The issue is, I have a search box at the top of the page, and when a town is typed in, a lat and lng is returned for that town (this bit is fine and quite straight forward).

I wish to then search for all properties that are within 2-3 miles of that returned lat and lng. This uses a complex SQL query found on this page - https://www.plumislandmedia.net/mysql/ha...st17058704

I basically want to integrate this, however I'm struggling on working out the best way to integrate it into CodeIgniter using framework helpers as much as possible.
I currently have a version of this working, by manually calling a function in my Model which passes the lat and lng to a raw sql which returns a result array.
This approach doesn't feel right, and I can't use the helpers for pagination. Eventually, there will be thousands of listings, so pagination is definitely needed, and I want to keep things as tight and hack free as possible.

To sum up...

If no search input, then allow the model to return all paginated listings (as it does now). BUT if a search term is entered, how do I use the complex query in a clean framework friendly way which will return paginated results.

Many thanks

I'm not after all the code to achieve this by the way, I simply would like some advice as to how to structure this within my Controllers and Model(s). Although some basic pseudo code would be helpful regarding what should appear in my controller and model depending whether a search is submitted or not.
I've also tried to use the QueryBuilder helper to form the query mentioned above with little success. Is this possible, or will I have to resort to some raw sql? Potentially I could use the Querybuilder with raw sql in the Join and Where?

See https://codeigniter4.github.io/CodeIgnit...ith-models

What did you do? What did you get?

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Ok, so I managed to integrate a basic test query with pag based on the support webpage you shared so thanks for that.

I'm now trying to build the complex query referenced in my original post, using the Builder class to form my query. I'm currently getting the error...

Code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 53.000000 AS latpoint, -2.400000 AS longpoint, 30 AS radius, 69 AS dist' at line 3

This is the snippet from the builder() in my method

PHP Code:

$this->builder()->join('', new RawSql(('SELECT  '.$lat_degrees.'  AS latpoint, '.$lon_degrees.' AS longpoint, '.$distance.' AS radius, '.$distanceUnit.' AS distance_unit) AS p ON 1=1')), ''); 


The join sql i'm trying to integrate is as follows...

PHP Code:

JOIN (SELECT  '.$lat_degrees.'  AS latpoint, '.$lon_degrees.' AS longpoint, '.$distance.' AS radius, '.$distanceUnit.' AS distance_unit) AS p ON 1=1 


As a reminder, the query I'm using is here - https://www.plumislandmedia.net/mysql/ha...st17058704

I'm assuming if I use the Codeigniter Builder Class, the pagination will work automatically?

Many thanks

Yes, if you use Query Builder methods, the pagination will work.

But it seems we cannot write the join like the following with Query Builder.

Code:

  JOIN (  /* these are the query parameters */
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint,
                50.0 AS radius,      111.045 AS distance_unit
    ) AS p ON 1=1

See https://codeigniter4.github.io/CodeIgnit....html#join
The first parameter is a table name.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Yes, I am struggling a bit here. It doesn't appear to be a conventional join does it? I've tried messing a bit about with various uses of RawSql helper but to no avail.
Any suggestions?

If you don't extend Query Builder, you cannot write the SQL statement that you want.

I recommend you use $db->query() https://codeigniter4.github.io/CodeIgnit...ar-queries
and Manual Pagination https://codeigniter4.github.io/CodeIgnit...pagination

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper