Modern advanced sql generation from an array of criteria

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

thinsoldier wrote:I'm mostly focused on just taking criteria, discarding any disallowed criteria, and generating sql from the criteria. The super long term goal is to have the querying of data availalbe through a single central object that can be used easily with minimum php code or exposed at its own REST url (probaly only responding to GET requests) for js-based sites or native apps to consume.

Code: Select all

$instructions = [
'criteria'=>[
        'not-expired' => 1,
        'not-hidden' => 1,
        'application-completed' => 1,
        'category' => 'commercial sale',
        'island' => 420,
        'property-type' => [71,23,44,11]
    ],
'config'=>[
        'format' => 'json',
        'paginate' => 1,
        'paginate-limit' => 20,
        'paginate-offset' => 60
    ]
];
$data = new ListingsQuery( $instructions );
Let me start a rough example of a Search Model and see if it is what you want. Looking at the data, my first comment is that your 'criteria' values are what the Model should deal with. Your 'config' are really for View code to deal with. There should be a separation there because, for example, the public and admin search forms can use the same model, but the admin search might use more criteria in the Model that the public search does not use.
thinsoldier wrote:would hopefully return the same data as

[syntax]
http://
example.com/
datasource.php?not-expired&not-hidden&application-completed&category=commercial+sale&island=420&property-type=71,23,44,11&json&paginate&pag-limit=20&pag-offset=60
[/syntax]
Not sure what you mean "return the same data as"? Do you mean the form?

Anyway, here is a quick Model class if you wanted to code it directly. This version uses direct setters. It could also have a more declarative interface.

Code: Select all

class SearchModel
{
        protected $db;
        protected $not_expired = 0;
        protected $not_hidden = 0;
        protected $application_completed = 0;
        protected $category = 'commercial sale';
        protected $island = '';
        protected $property_types = array();
        protected $offset = 0;
        protected $limit = 0;
 
    public function __construct ($db)
    {
        $this->db = $db;
    }

    public function setNotExpired ($not_expired)
    {
        $this->not_expired= $not_expired;
    }

    public function not-hidden ($not_hidden)
    {
        $this->not_hidden = $not_hidden;
    }

    public function applicationCompleted ($application_completed)
    {
        $this->application_completed = $application_completed;
    }

    public function category($category='')
    {
        $this->category = $category ? $category : 'commercial sale';
    }

    public function island($island='')
    {
        $this->island = $island;
    }

    public function propertyTypes ($property_types)
    {
        if (is_string($property_types)) {
            $property_types = explode(',', $property_types);
        }
        $this->property_types = $property_types;
    }

    public function limit ($offset, $limit=20)
    {
        $this->offset = $offset;
        $this->limit = $limit;
    }

    public function search ()
    {
        $sql = 'SELECT id,address,city,state,zip,price,realtor FROM properties WHERE ';
        // add constraints based on settings here
        // execute query
        // fetch rows
        return $rows;
    }

}
Unfortunately I am out of time, so cannot do the part you probably care about, which is the search() method. Not sure if I am going the right direction here. And I don't know if you also need an example of the form handling code. The search would first validate the input to confirm the submission. I if the form did not validate, it would redisplay the form with errors and restore the inputs. If accepted you would configure the SearchModel, fetch results and display the search results.
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

I wish I hadn't put "form" in the topic now.

I'm not worried about the "form" part of it anymore.

Transforming the criteria into a valid and flexible sql query is what I want to improve.
[text]public function category($category='')
{
$this->category = $category ? $category : 'commercial sale';
}[/text]
There's actually over 100 fields in the listings table. I'm hoping some __get and __set magic will help avoid needing a method for every field.
Not sure what you mean "return the same data as"? Do you mean the form?
I mean I'm only concerned about it either ruturning raw sql or a php array of result data or a json string of that data. Not worried about html output.

If you look at this code: https://gist.github.com/thinsoldier/b30 ... b1de8da4d4

what that is doing is what I would like to improve. It looks at the criteria and generates SQL based on that.
Your 'config' are really for View code
Let's say I only used this code for an api that outputs JSON. Part of the criteria an app consuming that data would need is the ability to limit the number of results returned and what the offset of that data is so that in the app's next api request it coud get the next offset segment of data.
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

thinsoldier wrote:There's actually over 100 fields in the listings table. I'm hoping some __get and __set magic will help avoid needing a method for every field.
With that many fields, I think I might pass a Form Model to the Search Model. That way there is a clean interface to get those 100 values. I suppose it could just be an array, but wrapping it in a model might clean up your form code as well.
thinsoldier wrote:I mean I'm only concerned about it either ruturning raw sql or a php array of result data or a json string of that data. Not worried about html output.

Let's say I only used this code for an api that outputs JSON. Part of the criteria an app consuming that data would need is the ability to limit the number of results returned and what the offset of that data is so that in the app's next api request it coud get the next offset segment of data.
I think the model just returns all the rows that match the search. It is up to the View code to return HTML, JSON, etc.

I took a look at your code. Some things cannot be simplified, but there is a lot of duplication that could be reduced. And making a class might make utility methods easier to write. And I would the invert things to have the Model passed to the View instead of includes inside a function.
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

Christopher wrote: And I would the invert things to have the Model passed to the View instead of includes inside a function.
Yeah. That code is actually an obese controller method that is full of query building stuff that should be put somewhere else (like a query building object/model) which then passes the $sql to the view.
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced search form examples

Post by Christopher »

I can certainly show you what I might do to convert your function findlotsAction() into an object that has more reuse and might be easier to maintain.

Here is some pseudo-code about how I think about managing forms like this. Not sure if this helps, but maybe it gives you an idea about my comments above about the kinds of separations I am thinking about.

Code: Select all

$formModel = new FormModel($request);

// configure Form Model

if ($formModel->isValid()) {
    $searchModel = new SearchModel($db, $formModel);

    $resultsView = new SearchResultsView($searchModel);

    $response->setContent($resultsView->render());
} else {
    $formView = new SearchFormView($formModel);

    $response->setContent($formView->render());
}
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Modern advanced search form examples

Post by thinsoldier »

I'm not concerned with the plumbing around separation of concerns. I understand it and currently going through a series of tutorials on Laravel, Symfony, & roll-your-own-mvc-with-composer.

From your example, what's going on in the SearchModel regarding generating sql and $formModel having the option of just being a simple associative array is where i'm focused.

Also not interested in auto-generating form html or displaying validation errors. This code that currently exists in 1 controller I want to turn into something that can be used in many places like a "service" or "helper" or "utility" (not sure if those are the right terms). And most of the time it won't be used in conjunction with an html form. Just create a new object, pass it a hard coded criteria array, and get the sql text or sql results array. Generating the sql from that criteria is where I feel this code is messy and buggy and probably should be using a query builder or something. I'd like to find some open source project that has a large and complex example of converting a large number of possible criteria options into a large and complex sql statement.
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Modern advanced sql generation from an array of criteria

Post by Christopher »

Ok. I think I understand that you want the Search Model that is passed an assoc array of the form values. When I get a little time, I will try to refactor your findlotsAction() function into a class that might make it simpler and easier to maintain.
(#10850)
Post Reply