进行动态查询的最佳方法是什么(使用PHP + MySQL)? [英] What's the best approach to make dynamic queries (using PHP+MySQL)?

问题描述

我正在开始一个新项目，我可能要做出最重要的设计决定.

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

我将使用PHP即时创建动态查询. (我想为用户提供类似于MS Excel中动态表的功能.

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).

我有25张表，每张表大约有4-6列，我将根据用户选择进行联接(大多数操作将根据一些可能是5到5的值过滤器来计算AVG，SUM，COUNT等) 6张桌子就消失了.

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).

我正在考虑动态地进行所有这些JOINS和SELECTS以及GROUPS BY，但是看起来这将变得非常复杂，因此我开始考虑使用视图来降低动态构建的代码的复杂性查询.

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.

我还考虑避免在查询中使用GROUPS BY，并使用循环和代码中的数组来计算聚合函数.

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.

推荐答案

唯一有效的答案就是为此创建自己的框架.我已经做了很多次了.您想要的东西或多或少看起来像一个复杂报表生成器，可以即时生成报表，但是您想要创建一个带有可视化帮助对象的复杂查询生成器.

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.

我要做的第一件事是使用一个表示每个表并提供描述表字段的机制的模型，以便您可以向用户显示字段.然后在模型中创建一个链接机制，说:如果我链接此表和此表，则应使用什么JOIN.

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.

祝你好运

这篇关于进行动态查询的最佳方法是什么(使用PHP + MySQL)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！