How to write database framework Medoo

Aug 29, 2020 PHP SQL DB framework Medoo

I happened to have an opportunity to use Medoo, so I will leave it as a memorandum.

What is Medoo?

A database framework for PHP. (The official website is here)

Structure

Here is what I used. The order of placement is as follows.


select(
  "Account",
   [
     "[><]Shop" => ["Shop" => "id"],
     "[><]Product" => ["Product" => "id"]
   ],
   [
     "Account.Id",
     "Account.Name",
     "Account.Tel",
     "Shop.Id(Shop_id)"
     "Shop.Name(Shop_Name)",
     "Product.Name(Product_Name)"
   ],
   [
      "AND" =>
        [
          "Account.Id" => 1,
          "OR" => [
             "Account.Name[~]" =>'Tanaka',
             "Account.Name[~]" =>'Suzuki'
          ]
        ],
     "GROUP" =>
       [
         "Account.Id"
       ],
     "ORDER" =>
      [
        "Account.CreatedDate" => "DESC"
      ]
   ]
);

How to write

1) JOIN the table

Add the following contents to the beginning of the table you want to JOIN according to the JOIN method.

``’’ sample.php // [>] == LEFT JOIN // [<] == RIGH JOIN // [<>] == FULL JOIN // [><] == INNER JOIN

select( “Account”, “[><]Shop” => [“Shop” => “id”]


<h3>2) Add an alias</h3>
It is the "AS clause" in SQL.
You can give an alias called "Shop_id" to the Id column of the Shop table by the following method.

``` sample.php

[
  "Account.Id",
  "Shop.Id(Shop_id)"
]

3) Ambiguous search

Add [~] after the column name to be searched.


 "AND" =>
   [
     "Account.Name[~]" =>'Tanaka',
   ],

4) Exclude duplicates

At present, there is no way of writing equivalent to “distinct” in SQL. There may be various ways, but I replaced it by using GROUP. (Same as SQL GROUP BY)


"GROUP" =>
  [
    "Account.Id"
  ],

Summary

I didn't find much information when I looked it up. It seems best to check official documentation.

If your SQL isn’t that complicated, you can try using Medoo.