CODEWITHSUNDEEP
phpmodel-view-controllermodelcrud
methodin
methodin

Reputation: 6712

In a relatively simple CRUD model system in PHP, where would complex joins fit in?

I am working on an architecture redesign at my work and we've basically settled on a loosely-basic MVC custom solution. The intentions are to have the standard CRUD operations plus additional list operations defined in each of the models in our system.

Unfortunately about 30% of the code in our system uses complex joins and otherwise advanced querying that doesn't fit this model. Which is to say it could fit the model, but the list function would be huge and certainly error prone which is something we are trying to solve with the rewrite.

Given that, where would you place complex and very specific queries in such a system? We've been toying with a few options.

  1. Add multiple versions of list/get in addition to the basic ones
  2. Add in custom models for these queries that reside as siblings to the model directory
  3. Don't use models in this situation and add the work directly in the action

We have outsourced help as well so we are attempting to keep it as simple as we can in terms of implementation and maintainability. ORM solutions or other heavyweights are out of the question.

Where would you want to see such things placed as a developer?

Upvotes: 1

Views: 846

Answers (4)

gedq
gedq

Reputation: 610

Models are workers - if you have 100 reports you're potentially going to need 100 models. Joins have nothing to do with MVC - how your data is addressed is another pattern altogether. If you're not using ORM and you're not using active records then all that's left is sending the SQL straight to the server via a model. Probably via a dedicated database class but the model will handle the query and its results.

Upvotes: 0

niggles
niggles

Reputation: 1034

I have similar issues in am MVC framework I've been building from scratch.

I don't particularly like the overhead of SELECT * on complex queries so I didn't build any of that functionality in whatsoever.

It's slower to code, but I code every query by hand in the relevant class (my model calls a Class 99% of the time).

For really complex queries shared amongst various routines, I have functions that return the generic joins and then concat the additional parameters for that particular query.

Example provided as requested:

private function returnFindClientRequests(){

        $query = "SELECT 
        SR.sign_project_name, SR.module_signregister_id_pk
        ,SRI.module_signregister_sign_id_pk,SRI.sign_location_address
        ,SRR.status, SRR.module_signregister_item_client_request_id_pk, SRR.client_comment, SRR.requested_by_user, SRR.date_created
        ,SRR.admin_comment, SRR.date_actioned
        ,CL.client_name, CL.module_client_id_pk

        FROM 
        `module_signregister` SR, `module_signregister_item` SRI, `module_signregister_item_client_request` SRR, `module_client` CL
        WHERE
        SR.module_signregister_id_pk = SRR.module_signregister_id_pk
        AND SRR.module_signregister_sign_id_pk = SRI.module_signregister_sign_id_pk
        AND SRR.requested_by_group = CL.module_client_id_pk
        AND " . Database::groupQuery('CL');

        return $query;

    }

This query is shared amongst some other functions but also uses a call to Database::groupQuery() that us used to return session specific variables to many of the queries.

Upvotes: 0

grncdr
grncdr

Reputation: 956

I apparently lack the privileges necessary to comment, so I'm posting this as answer...

Could you provide an example or two of the kinds of queries you have that don't fit into a model? Generally speaking: a good ORM will get you a long way, but some queries really are just too hairy to map easily, and if your team already has strong SQL skills the ORM can also seem like it's getting in the way.

Upvotes: 2

Poelinca Dorin
Poelinca Dorin

Reputation: 9703

First , all you're queries should stay in you're model . Second , most of mvc frameworks provide more than just simple crud for you're database operations like a query functionality that where you can pass the query string , in this case you can build you're queryes manualy or with a query builder like for example Zend_Db_Table_Select and that handles multiple joins prety well . Or again if we look some place else than Zend let's say Codeigniter , it still provides a query builder for the model where you can add you're joins or build any other kind of complex queries .

That being sayd , it looks like you're base model class ( the one you extend each of you're models ) needs a query builder functionality , then you should be all good as you would be able to build any query you like inside any model you like .

Upvotes: 0

Related Questions