Published on
Part 1: Basic table listing
- From the ChronoConnectivity 6 admin home page, open the "Connections manager"
- Click "new"
- Enter your connection title.
- Go to the "Events" tab, events are like pages, a connection has 3 events by default but you can add more, an event page will do nothing unless you add some content to it, you can either add HTML code or some Connectivity 6 instructions, you can find a list of the supported instructions at the end of this tutorial.
- Since we need to display a list of items, we need to read this data from a database table, the "Functions" section can help us on this task.
- Go to the "Functions" tab and click "Database" then select "Read data".
- There are plenty of options to configure here, but for a basic listing just fill the "Model name" with "Article" and select the Joomla content table, it's name is #__content, where #__ is your database prefix.
- Please take a note of the "function name", its "read_data1" by default, we can change that to "read_articles", and it's strongly advised that you don't use spaces or special characters except - and _ in any "name" field in your app because this may cause conflicts, also names should be UNIQUE, this means that no 2 functions or views should have the same name.
- Now if we go to the "Events" tab and call our new database read function, the connection will retrieve articles in our Joomla database, we can run the new function by writing this code in the "Index" event box: {function:read_articles}, or for simplicity we can use {fn:read_articles}
- By writing the command above, the function will be executed when the event page is accessed, but the data is stored into a variable and need to be displayed somehow, so we will need to use a "View" to accomplish this.
- Let's go to the "Views" section, Connectivity 6 has a "Table" view which can receive the data list of our database read function and display it nicely, to create a new "Table" view, click the "List views" item then select "Table".
- Our new table is called "table1", and we will leave it as its, then we need to provide this table view with the data to be displayed, to do this we need to call the var which holds the data retrieved from the database by the read function, so we should write {var:read_articles} in the "Data provider" setting of our new "table1" view.
- When any function is called (we called our function in the "index" event), the returned data of this function can be used by calling {var:FUNCTION_NAME} and since our database read function was called "read_articles", we had to use {var:read_articles} in the table data provider setting.
- Now we need to set a list of columns for our table to display from the data set provided by the database.
- The data returned by the database has this stucture: [0 => [Model => [field1, field2]], 1 => [Model => [field1, field2]], 2 => [Model => [field1, field2]]], what the table does is that it loops on the data set, one by one, and so we will need to add the columns list in this format: Article.field:Header
-
So let's use the following list of fields:
Article.id:ID
Article.title:Title
Article.state:Published - Now let's go to the "Index" event and make sure that our new view is actually processed, we had a single line here before {fn:read_articles}, now we will add another line to call the table view: {view:table1}
- Save the connection and click the "Open" link in the connections manager!!
Part 2: Ordering and sorting
In Part 2 we will get the list to be auto sorted by the article id and provide a sorting link for the "Title" field, you must read Part1 first.
- Open the "articles" connection and go to "Functions" > "read_articles".
- Scroll down to "Sorting settings" > "Order fields", write "Article.id/desc".
- Save and check the list, we could use "Article.id/asc" for an incresing order.
- Now to make the title field "sortable", go to "Functions" > "read_articles" > "Sorting settings" > "Sortable fields" and enter "Article.title".
- Our function is ready to sort by this table field now, but we need to add the sorting link "view", so we go under "Views", click "List elements" and select "Sorting link".
- We change the name to "title_sorter", write "Article.title" in the "Field" setting, and write "Title" in the "Content" setting.
- Now open our table1 view by clicking the link on the left side, and in the "Columns list", we replace "Article.title:Title" with "Article.title:{view:title_sorter}".
- What we did here is that we used our view instead of the default text header.
- Let's save the connection and test.
- Our connection is now sortable by the Article title, in Connectivity v6, you can have more than 1 active sorting field.
- v6 is very flexible, you can use this sorting view link anywhere in your event to sort the data, for example, we can head to the "Index" event and add {view:title_sorter} BEFORE the {view:table1}, and that would display a sorting link outside the table.
Part 3: Basic filtering
In part 3 we will demonstrate how you can filter the data retrieved from the database table.
- Open the "articles" connection, and go to "Functions" > "read_articles" > "Filtering settings" > "Where conditions".
- Let's say that we want to display the published articles only, the "published" status is stored in the "state" field in the content table, and so we will have to add the following line to the "Where conditions": Article.state:1
- Please note that the "Model name" must be used.
- Now only the published articles will be displayed whenever the list is loaded, but what if we want to change the the state value based on a URL (link) parameter, we can do this easily by updating the line we have just added: Article.state:{data:published}
- The new change sets our condition to get the value from the url "published" parameter.
- What if the "published" parameter is not available ? we can specify a default value too: Article.state:{data:published/1}
- Now our list will display the published articles by default and we can control this using the "published" url parameter.
Part 4: Retrieving data from more tables
In part 4 we will show how you can create a simple table relationship to get the article's author username and display it in the table.
- Open the connection and go to "Functions" > "read_articles".
- Write "Author" in the "New model" box and click the "Add new model" button.
- Wait for the new model to be added and then click the new "Author" label in the models menu to open the new model's settings section.
- Select the "#__users" table from the "Database table" dropdown.
- We want to associate this model to the articles table and so we should write "Article" in the "Related to" box.
- From the "Relation" dropdown, select "One matching record, foreign key in the related table", that's because we want to retrieve one single author record for each article record, and the foreign key is actually stored in the "Article" table.
- in the "Foreign key", write "created_by", which is the field holding the "user id" field in the articles table.
- The author data should now be retreived along with the article record, but in order to display the author data, we have to add the desired field name to the table view.
- Go to "Views" > "table1" and add this line to the columns list: Author.username:Created by
- Save the connection.
Part 5: Customizing a field value in the listing
In part 5 we will convert one of the fields values in the table to a link pointing to a record details page.
- Open the "articles" connection and go to "Views" > then from the menu select "Elements" > "Link".
- Change the name to "article_view_link", since we want this link to point to an article details view page.
- In the "Content" box, write {var:table1.row.Article.title}, to understand this, the table view should be explained briefly, what the table view does (and the repeater view or the loop function) is that it iterates over the records provided to it, while doing that, the data of each row is available inside the iteration as {var:table1.row}, and here we are trying to get the value of the article's title inside the link view, and the link view is actually processed inside one of the table's rows, which is one of table iterations, and so we have to reference the table row data and then extract the article's id.
- We need to pass the article id to the view page, and so we need to write this in the "Parameters" field of our link view: article_id={var:table1.row.Article.id}, again, just like what we did with the article title, but here we are getting the article's id instead.
- Now lets switch to the "table1" view, and in the "Columns views", we should add this: Article.title:{view:article_view_link}, what this does is that it sets the content of the "Article.title" column to the link view we have just created.
- Save the connection and test one of the links, you open a blank page, we will demnstrate how to get the article data in Part 6.
Part 6: Loading a single record data
In part 6 we will go over how you can load the article's data for the view page.
- Open the "articles" connection and go to "Functions" then select "Database" > "Read data".
- Change the name to "read_article", select the "#__content" table and set the "Model" to "Article".
- In the "Where conditions" box, write this: Article.id:{data:article_id}, this is because we need to retrieve the data of the article which its id is passed in the link as "article_id", please check part 3 and part 5 in this tutorial for this info.
- In the "Data settings" section, set the "Select type" to "First matching record", we need to read one article only here.
- Now let's go to "Events" > "view" and write {fn:read_article} in order to run the "read_article" function when the view event is opened.
- Once the "read_article" function is processed, the data (assuming a record is found, we will cover the not found possibility in a later part) should be available under this var: {var:read_article}, and we can access any of the article's data like this: {var:read_article.Article.id}
- So, right inside the "view" event (or inside a view) we can write: <h3>{var:read_article.Article.title}</h3> to display our article's title inside a header tag.
- Save the connection and check the view page.
