Dynamic search, producing search result links (reverse routing)

Just pimping my cms and trying to simplify some usual tasks like searching. The cms has many different modules, each of these modules can be masked with dynamic routes.

The whole system is pretty solid and really flexible but this is where search result pages are causing a headache.

To keep things flexible i'm throwing basic data to my search cl开发者_如何学运维ass like this:

$search->addTable('content', array('title', 'excerpt', 'body', 'meta_keywords', 'meta_description'));
$search->addTable('event', array('title', 'description', 'tags'));

Then, my search class is generating one sql query from these variables which will find the results nicely (er, maybe 'nicely' isn't the best word now because the result set contains rows from different tables so i can't tell which row belongs to which table because all of the results are in a single array)

The problem is when i want to display the results page i need to take care of crafting appropriate urls for each result which is very difficult because of the systems flexibility i mentioned above (any content/module can be masked with anything).

I understand my question is very localized and maybe too vague to answer it in its current form so i'll try to clarify my problems a bit. I don't ask you about best practices or a simple answer for my problem but i'd be very thankful if you can provide me some good tips about these fundamental issues:

• Looks like producing a single query isn't the best approach because i can't tell which row belongs to which table (different tables could mean different url crafting methods). Another approach would be to query each table separately and store results in a multidimensional array (table name for the key). AFAIK querying sql within a foreach loop isn't an accepted method. Is it possible to mark each result row to reflect it's table name within a single query?
• Will it hurt my SEO if i won't craft the real (masked, routed) urls for these results but i display the very basic ones like (example.com/content/nice-url-slug). Google may punish my sites because of duplicates - which isn't okay for me.

As always please ask instead of clicking close maybe i can clarify the scenario a bit more. Thank you, fabrik.

---

The short answer is that it is perfectly OK to do multiple queries, so long as we're not talking dozens of them. This also means the results returned are going to be a set of sets, structured like your array above, with a table name being the key to a set of results from that table.

I have to figure that there is a point somewhere in the code where that array is assembled. When it is assembled, you can loop through it and query each table exactly once. Within that loop you have all of the context you need: the table name.

Then you return that array of arrays to whatever client is assembling it into results. If the tablename is enough to construct the URL you are good to go. If you need more information about each table, such as what its ID column is named, structure the results array to contain needed meta-data about each table.

Overall, it looks like you've got this structured well, and are getting tripped up trying to avoid multiple queries. Don't worry about that. The problem with multiple queries is when the same *table* is queried over and over in a loop, when that is the pervasive design pattern then performance tanks.