Implementing advanced SELECT functionality

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Implementing advanced SELECT functionality

Post by alex.barylski »

I think it's typically common practice to have at least CRUD operations for every table in a database...

Here my situation...I have a table which hold knowledgebase information, therefore some fields are:

keywords, content, date created, date updated, category, ranking...

Typically most users sort by rank or by relevance, etc...but they may occasionally include these criteria in their search patterns as well...

So now my trivial CRUD operations which previously worked on a single record using a PKID have faced a road block...

I could write seperate one-off search functions based on criteria:
- selectById()
- selectByKeywords() // paginated results & relevance ordering
- selectByAll() // paginated results & arbitrary ordering

I'm curious...given the situation, which I assume is quite common...how do you handle the situation?

Do you have a catch-all select function using advanced argument arrays/objects/structures or do you define and implement seperate functions like above? Perhaps you use a really cool SQL builder function passing it table names and generic filter criteria and have it return an query object as a parameter to all your high level SQL API functions??? The latter is what I'm trying to conceptualize...if it's possible...etc...

Cheers :)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Searching for equality is relatively easy to generalize: http://www.timvw.be/searching-made-easy/
If you want to take it further and use other criteria than equality things become harder ;)
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

Edit: Sounds like what I am trying to implement is a VIEW...although in PHP code not anything to do with SQL. Interesting... :P

Hey timvw...the phrasebook pattern is something like what I was looking for...the problem I see with it of course, is that advanced search criteria is often dynamic, meaning fields are optional...so a fixed SQL statement would not work. You would have to write several queries, one for each combination...which depending on the number of criterion could potentially grow into rediculous numbers.

I'm looking into the "possibility" of building an advanced QueryBuilder of sorts. One which allows seperation of SQL from PHP, keeping the SQL in an phrasebook of sorts but instead of being hardcoded, allows for some dynamic on-the-fly query construction...

Not sure how to go about doing something like this...

Ideally...you would pass a QueryObject the field names and the variable data...and somehow...the QueryBuilder would construct the relationships between tables and compile and execute the query, returning your resultset...

I have concluded that it's impossible to express anything but trivial table relationships using the expressive capabilities of PHP alone...this is why...PHP fails at OR/M...now if it had the expressive powers of C++ and retained PHP runtime capabilities...I might be able to express 1:1, 1:N and N:N relationships in native PHP...multiple inheritence in an OOP langauge might actually make sense here ;)

But alas, we are dealing with PHP...so I am thinking...perhaps a SQL-meta language would work best...

Any opinions...comments, suggestions???

Cheers :)
User avatar
Popcorn
Forum Commoner
Posts: 55
Joined: Fri Feb 21, 2003 5:19 am

my [in]coherent thoughts

Post by Popcorn »

I'm not sure I really understand but here goes ....

I figure no matter how you do it you're going to have to bite the bullet somewhere and start generating the SQL from all the criteria. I can only see allowing the user to specify not only more criteria but criteria of different types (string equality, string 'LIKE', numeric ranges, date ranges, etc. and AND/OR to combine the criteria) ending up with lots of parsing/constructing to be done.

Am I right in assuming that you're wondering where/how to do this?

How do I explain my approach... hmmm.... wrt current discussion I built a vanilla function to build the WHERE clause only. Pass args and criteria (like those mentioned above and optional or not, validate on the user side and/or later in the 'db template build system') and the fn returns a WHERE clause. The meat of the query is in a sort of templated query stored in the db and the setup to combine the two allows multiple customizations (for example a plain text ID column in the result or when an admin user is viewing the report, the ID value is also a link to an edit page).

I have not auto generated any of the JOINs (they are hard-coded in the template SQL). I suppose it could be done but there hasn't been the need yet.

I have to say I think my whole setup would seem pretty ugly to most (especially OO fanboys). But, it does seem pretty flexible and I have to say SQL seems pretty fundamental - I can't see too much point spending time abstracting away from it (Relational Algebra still needs clauses built right?)

Make any sense?

Ciao.

p.s. ".. expressive powers of C++" vs. "impossible to express anything but trivial table relationships using the expressive capabilities of PHP alone." - I feel a little out of my depth with this but ... Turing-complete so no prob right?
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

A QueryBuilder class sounds feasible - SQL is not rocket science after all...;)

I suggest rounding up alternative ORM implementations starting with the simple ones like Zend_Db in the Zend_Framework for inspiration. See how these get things done, and conceptualise a single Builder for SQL statements.

I rarely (if ever) have much need for complicated queries, so I generally implement CRUD, a few specific methods, and a general find() method which accepts standard SQL for those infrequent edge cases where something a QueryBuilder like class can't handle without help.

If you ignore in-depth abstraction for a moment, the typical Model (not sure if VIEW applies necessarily) will contain CRUD, and then specific methods for each additional special case. Often the Model methods are a further abstraction across an existing ORM solution since Models can have isolated APIs.

For most cases I just use a Singleton DataAccess class for SQL generations and execution where possible. Wish I had an open source version to post here, but it's style is pretty similar to what I put together in my SQL Data Object tutorial.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

Thanks for the feedback...but I was looking for something a little more specific...like ideas as to how to go abouts actually implementing a query builder...

I have ideas...but I always like to see how others might tackle the problem as well...

The problem with using a class or arguments to a function is it's difficult to express anything but trivial comparisons...atleast cleanly...sure you could use RPN but thats hard for humans to understand...

Hard to explain what I'm after exactly...something which makes advanced queries easier :P

Looking for theoretical ideas...improvments on existing technologies...and so on...

Cheers :)
User avatar
Popcorn
Forum Commoner
Posts: 55
Joined: Fri Feb 21, 2003 5:19 am

Post by Popcorn »

You should give an example of what you consider an 'advanced query', and which parts of the query are variable.
Post Reply