Build a basic listing

Details
Published: Monday, 23 January 2017 18:34

Part 1: Basic table listing


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.

  1. Open the "articles" connection and go to "Functions" > "read_articles".
  2. Scroll down to "Sorting settings" > "Order fields", write "Article.id/desc".
  3. Save and check the list, we could use "Article.id/asc" for an incresing order.
  4. Now to make the title field "sortable", go to "Functions" > "read_articles" > "Sorting settings" > "Sortable fields" and enter "Article.title".
  5. 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".
  6. We change the name to "title_sorter", write "Article.title" in the "Field" setting, and write "Title" in the "Content" setting.
  7. 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}".
  8. What we did here is that we used our view instead of the default text header.
  9. Let's save the connection and test.
  10. Our connection is now sortable by the Article title, in Connectivity v6, you can have more than 1 active sorting field.
  11. 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.

  1. Open the "articles" connection, and go to "Functions" > "read_articles" > "Filtering settings" > "Where conditions".
  2. 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
  3. Please note that the "Model name" must be used.
  4. 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}
  5. The new change sets our condition to get the value from the url "published" parameter.
  6. What if the "published" parameter is not available ? we can specify a default value too: Article.state:{data:published/1}
  7. 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.

  1. Open the connection and go to "Functions" > "read_articles".
  2. Write "Author" in the "New model" box and click the "Add new model" button.
  3. 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.
  4. Select the "#__users" table from the "Database table" dropdown.
  5. We want to associate this model to the articles table and so we should write "Article" in the "Related to" box.
  6. 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.
  7. in the "Foreign key", write "created_by", which is the field holding the "user id" field in the articles table.
  8. 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.
  9. Go to "Views" > "table1" and add this line to the columns list: Author.username:Created by
  10. 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.

  1. Open the "articles" connection and go to "Views" > then from the menu select "Elements" > "Link".
  2. Change the name to "article_view_link", since we want this link to point to an article details view page.
  3. 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.
  4. 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.
  5. 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.
  6. 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.

  1. Open the "articles" connection and go to "Functions" then select "Database" > "Read data".
  2. Change the name to "read_article", select the "#__content" table and set the "Model" to "Article".
  3. 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.
  4. In the "Data settings" section, set the "Select type" to "First matching record", we need to read one article only here.
  5. 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.
  6. 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}
  7. 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.
  8. Save the connection and check the view page.