Universal getDATA() function that allows input of unlimited WHERE case 1

[jpo245]

Hi everyone,

I have a database with about 18 related tables, and i realize that I will be retrieving data with a LOT of different where statements.

For example,

initially i was thinking of programming separate functions that retrieve data specifically according to hard coded SQL "WHERE" i put in.

So i would write;
getAllBooks(); //SQL no WHERE statement
getBooksOwnedByUser() //SQL has a WHERE user.username = XX
getBooksinCategory() // SQL has a WHERE cat.catname = XX
getBooksUserCat() // SQL has a WHERE cat.catname = XX AND user.username = XX


Needless to say, I'd be programming to infinity if i hardcoded the sql statement in each function, and end up with like 100 functions if i had a lot of related tables and alot of different "WHERE" requirements.

I was thinking of a better way to program a universal getBooks() function, where i pass into it an array of where requirements,
and then dynamically generate the WHERE clause.

I would put in a "WHERE 1" into the Base SQL case, and then just keep appeninding AND xxx.xxx = XXX AND xxx.xxx= XXX depending on what the WHERE requirements the array i pass in contains.

Ok thats the simple part.

THe problem is, the SQL will not be a simple one that just queries one table. I would also have to append in the "JOINS" because a lot of the fields are in separate related tables.


This is not even taking into account the order of the joins, which should be arranged such that the query is fastest.


Is this possible? Is there any existing example code that would allow me to do this? Or am i fated to programming 10000000 getBooksXXXXX() statements?


Many thanks....

 

[TonyGroves]

It should be possible.

A simple SELECT statement like you described consists of a string of lexical elements:
1. SELECT
2. fieldlist
3. FROM
4. table1 [JOIN table2 on CONDITION] ...
5. WHERE
6. condition [AND condition] ...

That should not be hard to code. However, you would need to be sure that your queries are going to remain as simple as you anticipate. And optimisation is one thing that would very hard to do; an automatically-generated query will never be as efficient as a hand-crafted query with the benefit of human insight.

 

[jpo245]

hi tony thanks for replying.
I pretty much figured out that i'd need to know the FIELDS, the TABLES, and the WHERE clauses.

Problem right now is figuring out what kinds of joins to use, and how to order tables in the "from" clause.
Obviously the the type of joins (mainly left/right) would depend greatly on the order of which the table names are spat out.

I have no idea how to code a logic such that it knows which tables should be left joined, and which should be right.

Im thinking of requiring another parameter where the user actually specifies the order/join type for each table that will be included.
After passing in, fieldA, fieldB, fieldC, from tableA,tableB, the user can also pass in tableARJtableB or something like that.

I think I will design a class that contains all the required information as it might be hard to put it in all one array.

This would facilitate ability to optimization the joins, and would require minimal time from the user.

Anythoughts on my method?

Thanks!!

 

[TonyGroves]

Mmm... It's not so simple, is it?

Regarding the order of joins, you shouldn't need to worry about that; MySQL is clever enough to figure out the most efficient order for itself.

However, join types are a different matter. Getting your program to decide on that will be very tricky. And expecting the users to specify that information is probably unrealistic; if you're going to do that, then you might as well get them to type in the SQL for your program to execute.

Maybe you could come up with a compromise solution: Write a set of query skeletons for each table-join combination, and get the program to pick the right one and generate the field-lists and WHERE conditions.

 

[jpo245]

thats true... but im having the scary feeling that i might be brainstorming a TON of query skeletons, and having a bigger problem getting the program to choose which is the right one based on the different tables used...