I had a tough time searching for examples over the net for all kind of symfony queries. Though symfony is well documented here, I didn’t find enough examples there.
If you have landed up on this post straight and are new to symfony, you may want to first read previous posts:
Getting started with Symfony – A PHP Framework – Part 1
How to build a login-registration system using Symfony – A PHP Framework – Part 2
If you are well equipped with the basics, read on
Lets start with the few basic example queries.
- Extract list of all users from the users table
Now lets see how can we use this $resultset within the model layer and in the template files.
To use the resultset within model layer, we can do something like this:
To use the resultset in template files (viewer layer), we can do something like this:
- Extract list of first 10 users only
- Extract list of 10 users starting from 100th user
- Extract user data where username = ‘imoracle’
- Another approach to do a select query
To access this resultset in the model layer itself, we can do something like this:
Where get(1) fetches the 1st column for you of the resultset. Hence $resultSet->get(n) will fetch you the nth column of the resultSet.
Lets see a few quick examples for inserting a new row in a table.
- Insert a user entry in user table
$new_user_id = $new_user->getId();
Finally we get the last inserted user row id.
Symfony provides nothing special for the update queries. The same above format works for updating a row as well. Symfony is smart enough to analyze whether its a select or update query.
- First Method for Update Query (Recommended)
In the above example we chose to update the password for user with username “imoracle”.
- Another Method for Update Query
Important code in above 5 lines is the 2nd line. Without this line it will add a new row and not update. Also another drawback which I found in this approach is that in third line you need to give the PK (primary key) of the table. Like our 1st method you cannot give username in the third line.
Finally I haven’t tried and tested this method enough. So I will not recommend this.
Its basically the same above methods, with change of final call.
- Delete a user’s account where username = ‘imoracle’
How to alter a table in symfony?
- Method 1 (Non-standard)
A drawback which I have seen in symfony till now is that, when you try to alter a table by adding new columns in your schema.yml, It flushes the data from the table and gives you an altered table with no data. This is really bad, if you don’t know this behavior and move ahead altering the table. Though Symfony provider propel-dump-data to handle alter tables, but it is still quite buggy and slow for big databases. The clean and smooth way to achieve this is:
And you have your new altered table without any loss of data.
- Method 2 (Standard)
If you are afraid of your data being flushed by the stupid symfony, then you may choose not to touch your database using propel at all. Here is what you can do in such a case.
Here propel-build-model will only rebuild the ORM layer to incorporate your schema.yml changes. However propel-build-all not only builds the ORM layer, but also actually creates the table in the database (flushing the data)
In future I shall keep adding more SQL queries on this post, as and when I write them using symfony. So keep glued to this one 😉