Junction tables and Medoo

I love bright and shiny things... I love them a wee bit too much to be fair. It seems to be something of a failing amongst developers to love bright and shiny things as we can have a tendency to be like Gadflies, fluttering hither and yon looking at and playing with the latest shiny thing. I have to admit I’m a wee bit like that with Medoo as I thought it was the best thing since sliced bread not a fortnight ago… then I noticed my proclivity for junction tables and I couldn’t get my poor poor head around the joining of tables within Medoo. The queries I was using were hard enough without having to also worry about using a whole different approach to selecting data with a new library… but then I had half an hour spare this afternoon whilst ‘er indoors was off being socially useful so I thought I’d have a proper crack at it and I think I’ve got it sorted!

My use case has three tables. The first is contacts and it holds basic details about a person such as their name(s), titles and all sorts of other things which should be useful at some point. My second table has addresses which are nicely formated and should last should address formats change in the UK anytime soon. My third and final table is a junction table where the contact id and address id are held. This allows for the person to have multiple addresses and the address to have multiple contacts associated with it. Should either the address or contact be deleted then the relevant row in my junction table will also be deleted by a trigger but without the corresponding contact or address being deleted.

It took me some little head-scratching to work out an appropriate query, this is it:

$query = $dbconn->prepare("
   SELECT
       T4.*
   FROM
       contacts T1
   JOIN
       contact_address T3
   USING
       (contact_id)
   JOIN
       addresses T4
   ON
       T3.address_id = T4.address_id
   WHERE
       T1.contact_id = :id
");
$query->execute(array(":id" => $_GET["id"]));
$address = $query->fetch(PDO::FETCH_ASSOC);
var_dump($address);

This works a treat but I want to use my new toy to get the same data. Once I’d scratched my head some more (and yes, I know, I’m not sure where T3 went to either), and read the Medoo documentation a little more, I clocked that it was really rather easy:

$address = $db->select(
   "contacts",
   array(
       "[>]contact_address" => "contact_id",
       "[>]addresses(a)" => "address_id"
   ),
   array(
       "a.address_id",
       "a.line_1",
       "a.line_2",
       "a.line_3",
       "a.line_4",
       "a.postal_city",
       "a.postal_county",
       "a.postal_code",
       "a.government_region",
       "a.postal_country"
   ),
   array(
   "contact_id" => 145
   )
);
var_dump($address[0]);

Apart from having to specify each column in the return array and taking into account the fact that it returns an array of results (which sort of makes sense TBH) it seems a much nicer way of doing things. Now I’ve got my head around this I think Medoo is going to be taking a place within my toolbelt of cool things to use when developing.