CODEWITHSUNDEEP
phpmysqlmodel-view-controller
Hockey Mike
Hockey Mike

Reputation: 43

MVC: Which Model to place my code in?

I am a newbie to web programming and MVC architecture.

I am creating a web application using an MVC architecture (along with PHP and MySQL).

I have written a SELECT statement that lists "available" contact persons that will be provided to a form and populate the form's select "Contact Person" field with a list of people from which to make a selection. The SELECT statement that I have written works fine as I have tested this from the MySQL command line.

What I am unsure of is the appropriate Model to place this code in (the PHP code at the bottom of this question). User or Company?

I think that Company would be the appropriate place as I have the following unique constraint in my "company" table:

CONSTRAINT uc_company_contact_person_id UNIQUE (contact_person_id)
COMMENT 'A user can only be a contact person for one company.'

Here are a few of my Models and associated tables:

Company: Typical company fields and a "contact_person_id" field (NOT required, but if it exists, then it must be associated with a unique user.id)

User: Typical user fields and a "role_id" (Users must have an assigned role and they must have an assigned company to belong to)

Role: List of roles

Permission: List of permissions

role_permission table binds permissions to a role.

Example of my function to generate the list of "available" contact persons. Just not quite sure which Model it belongs in?

public function listAvailableContactPersons($selectedContactPerson)
{
   blah, blah, blah

   SELECT      user_account.id AS "User_ID", CONCAT(user_account.last_name, ", ", user_account.first_name) AS "Contact_Person"
   FROM        user_account, role
   WHERE       user_account.role_id = role.id
   AND         role.name NOT IN ('OWNER', 'DEVELOPER', 'WEBSITE_ADMIN')
   AND         user_account.id NOT IN (SELECT          user_account.id
                                       FROM            company
                                       LEFT OUTER JOIN user_account
                                       ON              user_account.company_id = company.id
                                       WHERE           company.contact_person_id = user_account.id)
   OR          (user_account.role_id = role.id AND user_account.id = :selectedContactPerson)
   ORDER BY    user_account.last_name;

   blah, blah, blah

   $stmt->bindValue(':selectedContactPerson', $selectedContactPerson, PDO::PARAM_STR);


   blah, blah, blah

   return $stmt->fetchAll();
}

Thank you in advance for your help,

Mike

Upvotes: 0

Views: 59

Answers (1)

Tim Morton
Tim Morton

Reputation: 2644

FWIW, I would consider the contact person's id to be a property of the company, per your schema:

Company: Typical company fields and a "contact_person_id" field

However, the way you've written the method seems to make the method belong to Users.

(I got a bit lost reverse engineering your query; the left outer join in the not in(select ... left outer join...) seems superfluous as it will never affect the size of the list of user_account.id's. So I'm not completely sure what your method is doing.)

Something to consider is using dependency injection to get the user data into the company object, ie, pass a user object into the function. This makes unit testing easier.

// consider this pseudo code; I use my own db abstraction so I don't remember
// how to do this in PDO...

Class Company {
  public function listAvailableContactPersons($userObject) {
    $query = "SELECT contact_person_id FROM companies";
    // prepare, bind, execute...
    while($record = $stmt->fetch()) {
      $user[] = $userObject->find($record->contact_person_id)->fullName();
    }
  return $user;
  }

The down side of this is the number of db calls you'll be making. A better way would be to get a list of ids and then pass that list to the user object.

Class Company {
  public function listAvailableContactPersons($userObject) {
    $query = "SELECT contact_person_id FROM companies";
    // prepare, bind, execute...
    while($record = $stmt->fetch()) {
      $users[] = $record->contact_person_id;
    }

  return $userObject->findFromList($users)->fullName();
  }

I've made some assumptions about your data; hopefully I was close. Obviously, you would have to create the User methods I referenced. This was just to give you an idea of what could be done.

Upvotes: 1

Related Questions