Database table relationships: One-to-One vs. One-to-Many vs. Many-to-Many

A lot of people have problem with relational databases and how to define relationships between two tables.

This is a guest post by Dave from saturnasoftware.com where he teaches us how to easily recognize the correct relationship:

General Rule and One-to-Many Relationship

Back when I taught database design at university I had a LOT of students who struggled with One-to-One vs. One-to-Many vs. Many-to-Many. Part of the problem is that English can be a bit confusing with plural forms: Person/People, Mouse/Mice, etc. Another part of the problem is that verbs do not always have exact inverses so we end up saying ‘Queen Elizabeth RULES England’ but then have to say the rather awkward ‘England IS RULED BY Queen Elizabeth’. There are so many slight variations in English that it gets confusing. Here’s a very simple way to ALWAYS get it right:

If you want to figure out the relationship between X and Y, complete the follow two statements:

  • Each X GOES WITH, at most, ____ Y
  • Each Y GOES WITH, at most, ____ X

Let’s assume we have Person and Country tables. For the sake of this example we assume that every person lives in only one country. So, for this specific case, we have the context of ‘where a person lives’:

  • Each Person GOES WITH, at most, ONE Country
  • Each Country GOES WITH, at most, MANY Person(s)

Now, keep the last two words of each statement…

  • We have [ONE Country] to [MANY Person]

A One-to-Many relationship is always “owned” by the MANY side, which is to say that the Foreign Key goes in the table for the MANY side (we put the Country identifier in the Person table).

Many-to-Many Relationship

A Many-to-Many relationship (use the same two statements to figure this out) is special; it requires a third table. Each of the two related items has a One-to-Many relationship with this new third table, so the third table contains TWO Foreign Keys, one for each related table.

So, in the context of ‘places a person has visited’, we have:

  • Each Person GOES WITH, at most, MANY Country(ies)
  • Each Country GOES WITH, at most, MANY Person(s)

Incidentally, this ‘third table’ is conventionally named for the other two tables, in alphabetical order: CountryPerson or Country_Person, but can be named anything (e.g. Visited). Naming it after the other two tables makes it easy to figure out what the purpose of the table is; alphabetical is just arbitrary.

One-to-One Relationship

A One-to-One relationship (again, same two statements) is super easy – it does not matter which of the two tables gets the Foreign Key. Almost always one or the other of the items logically “owns” the other one… but it technically does not matter.

The most common kind of One-to-One relationship, in my experience, is an “IS-A” relationship – like “A Customer IS-A User” or “An iPhone IS-A Mobile Device.” A trick that I use for IS-A type relationships is to use the same Primary Key(identifier) in both tables. That way either Primary Key can serve as the Foreign Key to the other table.

But One-to-One relationships are not ALWAYS IS-A relationships… for example…

In the context of “place that this person rules (as in King/Queen)”, we have:

  • Each Person GOES WITH, at most, ONE Country
  • Each Country GOES WITH, at most, ONE Person

Here we could put the Person identifier in the Country table OR we could put the Country Identifier in the Person table. Since there will likely be fewer Countries than Persons, I’d put the Person identifier in the Country table because it will result in less data to store/handle.

There you have it, the (very) short version of my ‘what kind of relationship is this?’ lecture.
Hope it helps someone.

Script php artisan clear-compiled handling the pre-update-cmd event returned with an error

I had a problem with Basset package (website didn’t load, it kept timing out) so I tried to remove it from the composer.json file to see if it helps. After running php composer.phar update I got this error:

Script php artisan clear-compiled handling the pre-update-cmd event returned with an error

You can even get this error:

Script php artisan optimize handling the post-update-cmd event returned with an error

To fix this error open your composer.json file and delete the scripts key (or just the part that fails – in bold). In my case it was this:

	"scripts": {
		"pre-update-cmd": [
			"php artisan clear-compiled"
		],
		"post-install-cmd": [
			"php artisan optimize"
		],
		"post-update-cmd": [
			"php artisan optimize"
		]
	},

I ran php composer.phar update again and it worked.

Some people report that after this they have problems with running php artisan optimize and few others . I didn’t have this problem but if you have it then delete the app/bootstrap/compiled.php file and then try running php composer.phar update

Simple relationship example in Laravel 4

Today I tried to create a simple relationship with Laravel 4 Eloquent but because I am still learning Laravel I spent more than an hour to figure it out. I searched the net ,forums etc but I simply didn’t find what I was looking for or it was for more complicated example. Relationships can be quite complicated ( like we all don’t know this 😉 ) so I am not going to go into details about what they are in this post – I am going to show you a specific solution for a specific problem.

Display the name of the country of specific person with Eloquent relationships

OK, so we have a table Customers and table Countries. Table Customers has fields like first_name,last_name, telephone etc but also field/foreign key country (int). Table Countries has the list of all countries in the world. The name of the specific country in table Countries is stored in the field short_name. We have admin and in the admin we have a page that lists all customers.

Defining the relationship between two tables

Customer can only have one country but country “has” many customers.(for example several customers can be from US). So in our case we have one-to-many relationship.

In the controller (that deals with customers) we have this code:

	public function getIndex()
	{
		// Grab all the customers
		$customers = Customer::orderBy('created_at', 'DESC')->paginate(10);
		// Show the page
		return View::make('admin/customers/index', compact('customers'));
	}

and in our admin/customers/index.blade.php view we loop through results set like this(excerpt):

      @foreach ($customers as $customer)
        <tr>
            <td>{{ $customer->first_name." ".$customer->last_name }}</td>
            <td>{{ $customer->country }}</td>
            <td>{{ $customer->telephone }}</td>
        </tr>
        @endforeach

Simple but it gives us this:

Relationships before, Laravel

Now we could create a function that would return a name of the country like this Customer::countryName($customer->country) but why do this when it can be done much more elegantly.

Laravel Eloquent relationships to the rescue

Open the Customer.php model and add this method/relationship:

    //relationship with country table 
   public function country() {
        return $this->belongsTo('Country');
    }

Now let’s go back to our admin/customers/index.blade.php view and check this line:

<td>{{ $customer->country }}</td>

We can now access the country name with this code : $customer->country()->first()->short_name

but it gets even better: Due to Eloquent’s Dynamic Properties we can shorten our call so replace the above line with:

 <td>{{ $customer->country->short_name }}</td>

Save, refresh your page and voila, we see this :

Relationship final laravel

As you can see, relationships in Laravel 4 can be very useful and elegant way to solve such problems. In this post I didn’t explain every funtion call because I didn’t want it to get too confusing. I will write more about relationships in some of the future posts.

Added: You can read more about database relationships here: Database table relationships: One-to-One vs. One-to-Many vs. Many-to-Many

Laravel 4 Accessors & Mutators

Eloquent provides a convenient way to transform your model attributes when getting or setting them. In Laravel 3 they were called Getter & Setter Methods. In Laravel 4 they are called Accessors & Mutators.

Accessors are custom methods in your Model that modify the value of a field before it is returned to you (e.g. before you display it on screen). Similarly, mutators are custom methods in your Model that modify the value of a field prior to being set (e.g. before being saved to database ). These methods are called automatically when you access or set the field as a property on the model (e.g. $movie->release_date).

Note: Laravel 4 adheres to the PSR-0 standard so the accessors / mutators’ method names have to be CamelCase. So, if you have an attribute called “release_date”, the appropriate mutator has to be called setReleaseDateAttribute() and the appropriate accessor has to be called getReleaseDateAttribute(). But enough of theory, let’s see an example.

Modifying date attribute with Accessors & Mutators

Let’s suppose we have a table “Movies”. The table has various fields like title,length, studio etc and of course release_date field (date). What we want to do is automatically modify/format release_date field before inserting it into database and before displaying it in our form. Now this is just for example but let’s suppose that in the database it is stored in Y-m-d format (2013-06-16) and in our form it has d/m/Y format (16/06/2013). I am using Carbon class for date conversion (you can find it in vendor/nesbot/carbon folder – it is included with Laravel 4) but you can use anything you like – even PHP date().

So open your Movies.php model (that extends Eloquent) and add this method somewhere:

//mutator for changing date d/m/Y format to Y-m-d format so it can be stored in database
 public function setReleaseDateAttribute($value)
    {
        $this->attributes['release_date'] = Carbon::createFromFormat('d/m/Y', $value)->toDateString();
    }

This might look complicated at first but don’t worry, it isn’t. As you can see we have a mutator method (setReleaseDateAttribute). If you are wondering why does this method have such name then please re-read the third paragraph of this post (about PSR-0 standard). Note that the value of the attribute is passed to mutator.

Let’s explain this line:

 $this->attributes['release_date'] = Carbon::createFromFormat('d/m/Y', $value)->toDateString();

We access the attribute “release_date” with $this->attributes['release_date'] and set/change it to whatever we want. In the right part we are using Carbon class to create ‘d/m/Y’ variable with $value and then convert it to Y-m-d format with Carbon’s toDateString() method. Again, read Carbon documentation. The right part can be anything you want :

 $this->attributes['release_date'] = '2013-06-13';

Of course that would manually set the date and ignore the actual $variable so this is just for demonstration.

Accessor works the same way, except that we use “get” instead of “set” in the method name. Also since it is just accessor we do not modify the $this->attributes['release_date'] – we just return a (new) value. So add this code below mutator code in your model:

//accessor for changing date Y-m-d format to d/m/Y format so it can be displayed in form field
 public function getReleaseDateAttribute($value). 
    {
       return Carbon::createFromFormat('Y-m-d', $value)->format('d/m/Y');
    }

As you can see we return the value with return and in the mutator we don’t – in mutator we set/modify the $this->attributes['release_date'] .(Also Carbon statement is a little different)

How does Laravel automatically know when to use accessor and when mutator?

If we access the variable somewhere in our controller or model (like $var = $movie->release_date;) then Laravel will call accessor before it is returned to us. If we set the variable (like $movie->release_date = "16/06/2013";) and then save it to database ( e.g. $movie->save() ) Laravel will call mutator before it is saved to database.

Note: If you want to use Carbon class that is included with Laravel 4 you can autoload it or simply add use Carbon\Carbon; right at the top of your model so it looks something like this:

<?php

use Carbon\Carbon;

class Movies extends Eloquent {
//your code, accessors and mutators here
}

Populate dropdown list from database with Laravel

While building admin/CMS or just a form on your site you might need to build a dropdown (select) list with values read from database. It is very elegant to do this with Laravel’s Eloquent. (For the sake of this post let’s assume that you want to read values from the database and not from some array stored in a class or somewhere else.)

Let’s suppose the database table is called “countries” and is already populated (You can get one here). It contains, you guessed it, a list of country names, which you would like to display in a HTML dropbox list.

First, create a model named “Country.php” (you can use artisan, excellent  Laravel 4 generators or create it manually. Keep in mind that the model name is singular (Country) and the database table is plural (Countries).

In your controller add:

$country_options = Country::lists('short_name', 'id');

Simple isn’t it? This will return an array of values like “[1] => Afghanistan [2] => Aland Islands”… (We are assuming that database field name containing country names is “short_name”).

Note: Personally I like Eloquent but in case you do not want to use Eloquent or/and do not want to have an empty Country.php model lying there just so you can populate drop-down then you can use Laravel’s Fluent Database Query Builder:

$country_options = DB::table('countries')->orderBy('short_name', 'asc')->lists('short_name','id'); 

Ok, now let’s send this to view with ->with :

return View::make('admin/customers/create')->with('country_options',$country_options);

In above case I am sending $country_options to admin view that is used to create customers.

In the view you display it like this:

 {{ Form::select('country', $country_options , Input::old('country')) }}

That is all it takes to have a functioning drop-down select box. As you can see we are using Laravel Blade template engine and Form class. With Input::old('country')) we re-populate it with old values (for example after the form is submitted and there are some errors)

How to combine more fields with concat

In another case (for “customers”) I needed to combine two table fields into one and then populate the drop down with it: customers “first_name” + “last_name” should be “first_name last_name”. You can do this by combining Fluent Query Builder with Eloquent:

$customer_options = Customer::select(DB::raw('concat (first_name," ",last_name) as full_name,id'))->lists('full_name', 'id');

As you can see we are mixing Eloquent and DB:raw method.

Note: If you only want to use Laravel’s Database Query Builder then you can do this:

$customer_options = DB::table('customers')->select(DB::raw('concat (first_name," ",last_name) as full_name,id'))->lists('full_name', 'id');

How to add default option to dropdown list

Sometimes we want to have some default value that gives us some instructions what to do (“Please select a country,…”) with the dropdown or maybe even some default “non-value” (in case user doesn’t want to select anything). The easiest way to do this is to add array('' => 'Select One') + so our code looks like this (few examples with the code in this post):

$country_options = array('' => 'Please Select Your Country') + Country::lists('short_name', 'id');
//or
$customer_options = array('' => 'Select Customer') + Customer::select(DB::raw('concat (first_name," ",last_name) as full_name,id'))->lists('full_name', 'id');
//and so on..

Keep in mind that you will have to add some validation (jQuery, Laravel’s own validation etc) in case user leaves the default option (in example above we leave the value as empty but you can set it to anything you want).

I really love Laravel 4, it makes developing so much fun and simple.

Laravel erorr: Call to undefined method Illuminate\Foundation\Application::getConfigLoader()

After I resolved my speed problems with Composer (see the post here ) I got this error once the Composer ended updating dependencies:

Fatal error: Call to undefined method Illuminate\Foundation\Application::getConfigLoader() in E:\path-to-my-project\vendor\laravel\framework\src\Illuminate\Foundation\start.php on line 106

As I already mentioned in my previous post I don’t know if this was due to me canceling composer updates several times or something else but the same error appeared when I opened my project URL in web browser.

The solution is simple: Delete bootstrap/compiled.php file.

After that run php artisan optimize command, which will generate new compiled.php file. I am not sure if this last step is necessary because everything seems to work even without the compiled.php file but I would advice to run this command just in case and regenerate compiled.php file.

Installing / updating Composer dependencies is very slow or time outs

I wanted to speed up my coding by adding Laravel 4 Generators and Laravel Artisan plugin for Sublime text 2 (you can learn more about this here). I added "way/generators": "dev-master" to my composer.json file as instructed and ran "php composer.phar update" and waited….and waited…and waited. How can downloading simple text files be so slow when I have very fast connection?

For some reason I always had problems with composers speed – installing or updating dependencies was very slow. By searching net I saw that a lot of people have similar problems and various solutions are offered – for some they work and for some they don’t. I would advise you to try them out and see if any of them help you.

One of the most common advices is to use the --prefer-dist switch:

php composer.phar update --prefer-dist

I ran it however it didn’t help me. Composer got stuck on updating symfony/filesystem dev-master. After 5 minutes I got this error:

[Symfony\Component\Process\Exception\RuntimeException]
The process timed-out.

After trying it few times with the same result I realized that git.exe was having some problems with my network (not sure what). So the problem seems to be with Git protocol, at least in my case. Composers default github-protocols config settings is ["git", "https", "http"]. So what I did was changed github-protocols to https with this command:

php composer.phar config -g github-protocols https

I ran the update again and it started working, much faster and no timeouts.

Note: I don’t know if this was due to me canceling composer updates several times but after composer finished updating I got this error for the first time :

Fatal error: Call to undefined method Illuminate\Foundation\Application::getConfigLoader()

The solution for this can be found here.

Google Sync Turned Off on my Android! How do I turn on Google Sync?

Yesterday I noticed that all my Google services were not being synced on my Nexus 7 (Android 4.2.2) anymore. Emails were not automatically downloaded, Calendar,Chrome, contacts etc were not updating. On top of that I was getting weird “Sync was unsuccessful” (or something like that) messages on my PC’s browser. Sure enough – sync was turned off on my Android tablet. For some reason option “turn on sync” is not easily accessible or at least not where you would expect it. If you go to Settings -> accounts then you will only be able to manually sync those accounts but not turn it on.

To turn on auto sync you have to go to

Settings -> Data usage -> Menu (top right corner, 3 small squares) -> check Auto-sync data

Here is the screenshot of the Menu:
turn on google sync auto-sync

How to remove horizontal scrollbar in Twitter Bootstrap tab function

While building my new admin/CMS with Laravel 4 and Gebo Admin (really cool twitter bootstrap responsive admin template) I encountered weird problem while using bootstrap tabs –  after I added “enhanced select” (Country dropdown) suddenly a horizontal scroll bar appeared!

How to remove horizontal scrollbar in tab function twitter bootstrap

I am not sure if this is 100% best solution (I am not that keen on digging into all this .js stuff) but the easiest way to fix this is to open Bootstrap.min.css, search for .tab-content and then replace

overflow: auto;

with

overflow: hidden;

Reload your page and the horizontal scrollbar is gone.

Enable Gmail Tabs – Tabbed Inbox

Gmail’s inbox Tabs are a very cool new Gmail feature that brings order to your inbox.

gmail inbox tabs

To enable it, click the gears button and try to find “Configure inbox”.

tabs inbox gmail

Click on “Configure inbox”, select the tabs that you wish to enable,click Save and you can now use the new feature.  In case you do not see “Configure inbox”, check back later.