2

I'm starting a new project and I'm about to take perhaps the most important design decision.

I'll be working with PHP to create dynamic queries on the fly. (I want to provide the users with someting similar to the dynamic tables in MS Excel).

I have like 25 tables with about 4-6 columns each, that I'll be joining depending on user selection (most operations will be calculating AVG, SUM, COUNT, etc depending on some filters of values that are probably 5 to 6 table joins away).

I'm thinking making all these JOINS and SELECTS on the fly as well as the GROUPS BY but it looks like it's going to be quite complicated, so I started thinking about using views to reduce the complexity of the code that dynamicaly builds the query.

I also thought avoiding GROUPS BY in the query and calculate aggregate funcitions using loops and an array in the code.

I would like to know which of these approaches would you recommend or if you have any sugestion or tip will be greatly appreciated.

SERPRO
  • 10,015
  • 8
  • 46
  • 63
JMaldo
  • 31
  • 4
  • This question is petty much impossible to answer. Most queries you'll do in a web app are dynamic. ```SELECT * FROM user u WHERE u.id = {id}``` is a dynamic query for example. Do you want to use a framework? or will everything you build be custom? Do you want to use an ORM? Will you use PDO or the basic mysql_query, mysql_connect etc. functions? What type of tables do you have? – Flukey Nov 24 '11 at 14:07
  • So, what is your question? I think you should not try to make views for every possible combination (to much work), neither should you use PHP to calculate values. MySQL will be faster (query optimization, caching and PHP is not fast anyways). – Benedikt Wölk Nov 24 '11 at 14:14
  • @BenediktOlek - With the right code optimisations, hardware, and tools PHP is quick. But agreed, if you're traversing an array of thousands of items to build a dynamic query then it wouldn't be the best approach... – Flukey Nov 24 '11 at 14:17
  • This question sounds very similar to this: http://stackoverflow.com/questions/108699/good-php-orm-library – nfo Nov 24 '11 at 14:26
  • @Flukey Everything I build will be custom. I'm sorry, when I said dynamic queries I meant dynamic queries a little more complex (with at least 2 or 3 dynamic joins). – JMaldo Nov 24 '11 at 14:46
  • @BenediktOlek I'm not trying to make views for every posible combination. I thought about making few diferent views with most of the columns and rows (depending on the particular case) and then I could simplify things by making easy dynamic queries similar to the one Flukey mentioned. Bottom line is I'm trying to avoid doing joins dynamically. – JMaldo Nov 24 '11 at 14:51

1 Answers1

1

The only answer that is valid is to create your own framework for that. I've done that quite a few times. What you want looks more or less like a complexe report generator that generates reports on the fly but you want to create a complexe query generator with visual aids for the client.

The first thing i'd do is use a model that represents each table and offers mechanisms to describe the table fields so you can show the user the fields. Then create a linking mechanism in your models that says: if i link this table and this table, what is the JOIN that i should use.

Let your user select the models to your, columns to use and then use your models to create the query for you. It actually works well but takes quite some time to do.

Good luck

Mathieu Dumoulin
  • 12,126
  • 7
  • 43
  • 71
  • 2
    There're a bunch of ORMs, don't you think questioner should base his code on one of them? Reenventing a bicycle might be not what he wants. – dmitry Nov 24 '11 at 14:19
  • I've never seen an ORM that allowed me to easily do that part. You might be right, i'm just giving an answer :) – Mathieu Dumoulin Nov 24 '11 at 14:20