ratna

Basic structure for arbitrary data

Recommended Posts

Hello,

first of all, let me thank everyone who is or was involved in this project. I mostly develop for WordPress and I'm sick and tired of this "CMS" - but hey, that's where the money is. For an upcoming personal project, I started looking for something that agrees more with my philosophies and I came across PW. From what I've seen so far, it's exactly how I would design a secure, stable, lightweight and fast CMS. I love the normalized database, the clean folder structure and the simple, yet powerful clutter-free core.

I've been digging through the API for the past two days, but I'm still not 100% sure what type of structure would make the most sense for what I'm trying to achieve. Here's a quick rundown of what a typical data set would look like:

Results
  |-- Category 1
        |-- User 1
        |-- User 2
        |-- User 3
  |-- Category 2
        |-- User 1
        |-- User 3
  |-- Category 3
        |-- User 2

The results page would list the different categories and each category would list all users that have a result in that category. A user can have results in one or more categories, or in none. The data will be manually entered by me, so it doesn't need to be an actual user account, just a unique entry. The user will also receive more arbitrary data for other areas of the website.

Now, in WordPress I would make a few normalized custom tables, create a plugin for the data entry on the backend and then display the individual tables via a shortcode that's embedded in the specific category pages, which would be children of the results page. However, from what I understand so far, this shouldn't be necessary with PW and should be solvable without custom tables or code (aside from the template files).

The results page would obviously be a page with a certain template and so would the category pages. What I'm not sure about is how I would set up the users and assign them the result data. Do the users have to be pages as well, with a template that includes all possible result types as fields that might be empty or populated? What threw me off when I tested this was the permalink that gets assigned to all pages, which makes me think you're not supposed to use them for things other than actual pages that are displayed.

Any input on how to properly solve this simple structure in PW?

Thank you!

  • Like 1

Share this post


Link to post
Share on other sites

As in a user has an article in that category? 
So your structure in the backend would be:

Articles
- Post
- Post
- Post
Categories 
- Category
- Category

The Template Post will hold the User or I guess "Published by"? And a Category will be a page reference on the Post template? If this is your structure this will work. Also lets assume a Post can be in multiple Categories. 

$results = $pages->find("template=post, title%=$searchTerm");

foreach($results as $result) {
	foreach($result->category as $category) {
		$categories[$category->title][] = $result->published_by;
	}
}

// Show Results
foreach($categories as $key => $value) {
	echo "<h3>$key</h3>";
	
	foreach(array_unique($value) as $user) {
		echo $user;
	}
}


I haven't tested this at all, but using my imagination I think it might work. If not it will definitely send you on the right track. I'm sure there is more efficient ways of doing this. However, this is what comes into my head first.

Share this post


Link to post
Share on other sites
Posted (edited)

Welcome to the forums @ratna,

On 10/9/2018 at 2:43 AM, ratna said:

Now, in WordPress I would make a few normalized custom tables, create a plugin for the data entry on the backend and then display the individual tables via a shortcode that's embedded in the specific category pages, which would be children of the results page.

Sounds painful! 🙂

On 10/9/2018 at 2:43 AM, ratna said:

What threw me off when I tested this was the permalink that gets assigned to all pages, which makes me think you're not supposed to use them for things other than actual pages that are displayed.

You can use them for anything. Routers, Containers, Pages, Settings, etc. With access control and/or published state and/or (in)existence of a template file,  url (permalink) will throw a 404 or can be forced to redirect.

Also, a page does not necessarily have to be viewed in its own template file. So, your results can be output anywhere...in your home template file, in another page, etc.

Back to you main question.

Some people say reading this helped 🙂

 

Your Page tree could look like below. Note, to avoid confusion and possible overwriting of system variables ($user and $users are API variables), I'm using Person and People respectively.

|-- People   
        |-- Person 1
                |-- Category 1 {this is not a page in the page tree; it is a page field}
                |-- Category 2 { -ditto all below -}
        |-- Person 2
                |-- Category 1
                |-- Category 3
        |-- Person 3
                |-- Category 1----------<
                |-- Category 2          |
                                        |
|-- Categories                          |
        |-- Category 1 -----------------^
        |-- Category 2
        |-- Category 3

We use People as a container for each person (what you called user). People can have a template called 'persons' with a matching template file 'persons.php'. We can use that to display all persons.

Person can have a template named 'person' with a matching template file called 'person.php'. The template person can have several ProcessWire fields, other than title. For relational data purposes, we create a multi-page field called 'categories' which can only contain 'category' pages. We add that to the 'person' template. Now you can choose what categories a user will have 🙂

The category pages can have a template called 'category' and matching template file...you know the drill by now. Same goes with their parent, 'Categories', with a template 'categories'.

With the above setup, you can search and list users by category and vice versa, search for categories and list the users under them.

Hope this helps.

Edited by kongondo
clarity
  • Like 3

Share this post


Link to post
Share on other sites

Thanks for the responses, it makes a lot more sense already. The structure that @kongondo describes seems to be what I need. However, just to double check, let me state again that none of this data will actually be displayed as a page of its own. I merely need a container to hold the data, which I can then query from the actual results page and render as a table. I'm trying to save this data by using the PW pages and fields system, so that I won't have to actually create a custom DB table.

For example, the custom DB table that I'd normally make for this would look something like this (not normalized for demonstrative purposes):

+----------+------------+------------+------------+
| person   | category_1 | category_2 | category_3 |
+----------+------------+------------+------------+
| person_1 |        500 |       1000 |       NULL |
| person_2 |       1000 |       NULL |       1500 |
| person_3 |        750 |        750 |       NULL |
+----------+------------+------------+------------+

The results page for category 1 would then query the DB with something like:

SELECT person, category_1 FROM results WHERE category_1 != NULL ORDER BY category_1 DESC;

The results would then be rendered as a table, listing every person that has an entry in this category.

Would this still be a valid usage for pages and fields, even though there would only be one actual, visible page for this simple example (the results page), with the rest of the pages merely being containers that hold data? I guess it's just difficult for someone new to PW to picture something called a "page" as anything other than an actual, visual page.

Share this post


Link to post
Share on other sites
1 hour ago, ratna said:

However, just to double check, let me state again that none of this data will actually be displayed as a page of its own. I merely need a container to hold the data

That's perfectly fine. It doesn't matter and it is not an issue. Devs do this all the time. 

1 hour ago, ratna said:

I'm trying to save this data by using the PW pages and fields system, so that I won't have to actually create a custom DB table.

That's OK. By the way, you'll find that 'all PW fields' are in a sense custom DB tables :-). By this is meant that there is no field that is actually required (yes, even the Title field, although by default it is marked as a required field in templates :-)). I get your point though - you don't want to create a custom field.

1 hour ago, ratna said:

Would this still be a valid usage for pages and fields, even though there would only be one actual, visible page for this simple example (the results page), with the rest of the pages merely being containers that hold data?

Definitely! At its most basic usage, a page is basically an entry (a row) in a database table called 'pages'! :-).

1 hour ago, ratna said:

I guess it's just difficult for someone new to PW to picture something called a "page" as anything other than an actual, visual page.

Yep. I suggest you read this classic post:

OK, some code (untested). 

Example code to use in your Results page template file.

// results page

// will hold our output
$out = '';

// get categories {limit and use pagination if you have lots of categories}
$categories = $pages->find("template=category,limit=10");
// make sure we got categories
if($categories->count()) {

	foreach($categories as $category) {
		// @note: use limit if you have lots of 'people'.
		// assumes the page field in the 'people' template is called 'category'
		$people = $pages->find("template=person,category=$category");
		// skip category if it has no 'people' OR you can just output below 'no people/users', etc
		if(!$people->count()) continue;
		$out .= "<h2>{$catergory->title}</h2>";
		// list users under this category
		// newish pw api rather than another foreach here.
		// @see: https://processwire.com/api/ref/wire-array/each/
		$out .= '<ul>';
		$out .= $category->each(function($c) {
			// append any applicable data you wish here; e.g. $c->bio (if people have a textarea biography field, etc)
			return "<li>$c->title</li>";
		  });
		$out .= '</ul>';
	}


}

// print out the output
echo $out;

That should result in something akin to:

<h2>Category 1</h2>
<ul>
	<li>Person 1</li>
	<li>Person 2</li>
	<li>Person 3</li>
</ul>

<h2>Category 2</h2>
<ul>
	<li>Person 1</li>
	<li>Person 3</li>
</ul>

<h2>Category 3</h2>
<ul>
	<li>Person 2</li>
</ul>

 

  • Like 1

Share this post


Link to post
Share on other sites

Thanks, i just implemented it and it works perfectly. Third day into PW and I'm already sold for good 🙂

Thinking about it, I actually find it useful to use the parent pages to display the available sub-categories, so it's a win-win. I ended up using the following structure:

Home
  |-- People
        |-- Person 1
        |-- Person 2
        |-- ...
  |-- Categories
        |-- Category 1
        |-- Category 2
        |-- ...

People and children are hidden.
Each category page uses a "category" template that displays a table with all people that have a populated field for that category.
No custom backend required, just the admin interface - loving it.

  • Like 3

Share this post


Link to post
Share on other sites

Great! Your illustration reminded me to edit my previous illustration because to the casual eye it would look like the categories under each person were child pages rather than page fields within the person pages 🙂

Share this post


Link to post
Share on other sites

I'm a little stuck again. In order to reduce the amount of fields, and because each result shares certain fields, I implemented a repeater field for the results of each person. The repeater field combines the category (select option) and a value (float). Each person can have none, one or multiple entries for each category. I've now been trying for several hours to display a sorted results table for each person in a certain category to no avail.

$people = $pages->find("template=person, results.category={$category}, sort=-results.value");

This doesn't work properly. The idea is to get all people with one or more results for the given category and sort them by the highest value they have in the repeater field for that category. Now, my next gripe is that, even if this worked properly, I pretty much have to repeat that selector when I loop through the people array to get the highest value within the repeater field.

foreach ($people as $person) {
	$person->results->get("category={$type}, sort=-value")->value;
}

This seems a little verbose and inefficient. Is this a limitation or am I doing something wrong?

Share this post


Link to post
Share on other sites
9 hours ago, ratna said:

Is this a limitation or am I doing something wrong?

Maybe this topic can help?

 

Share this post


Link to post
Share on other sites

Yes, @szabesz, I've read everything I could find about repeaters but no combination seems to work. It's a very simple SQL query, so it seems to me this should be possible.

SELECT * FROM people LEFT JOIN categories ON (people.id = category.person_id AND category.type = 1) ORDER BY category.value DESC GROUP BY people.person;

 

Share this post


Link to post
Share on other sites
15 hours ago, ratna said:

The repeater field combines the category (select option)

Do you mean you are no longer using a page field but a Select Options field? I.e., the category field in the repeater in your person template, is that a Select Options field rather than the original page field you had?

If that's the case, two things: 

  1. In your code, I don't know what $category is. In an Options field you cannot use an object as a search value
  2. You will need to search for a string within the Options field 'title' subfield, like so:
$category='Category 1';
$people = $pages->find("template=person, results.category.title=$category, sort=-results.value");

If my assumption above is wrong (i.e., you are still using a page field), then you probably have a typo somewhere?

Share this post


Link to post
Share on other sites
4 minutes ago, kongondo said:

Do you mean you are no longer using a page field but a Select Options field? I.e., the category field in the repeater in your person template, is that a Select Options field rather than the original page field you had?

I used float fields in the person template for each category before. But because a person can have multiple results for the same category and because the categories should be extensible, I switched to using a repeater field that combines a select options field for the category and a float field for the result.

The category variable uses the number that was assigned to that category in the select options field, e.g.

1=Category 1
2=Category 2
...

The code works and it iterates though all the people, but the sort order is not correct and seems to depend on the order of the repeater fields, rather than on the value. Unfortunately, the issue with the redundant and verbose queries to retrieve simple values remains as well.

Share this post


Link to post
Share on other sites
15 hours ago, ratna said:

 


foreach ($people as $person) {
	$person->results->get("category={$type}, sort=-value")->value;
}

This seems a little verbose and inefficient. Is this a limitation or am I doing something wrong?

There isn't anything inefficient about this (or at least there isn't really a more efficient way of doing it). The value of a Repeater field is a PageArray. When you do anything with a Repeater field value then the whole PageArray is loaded to memory. It doesn't matter if you only want a single page from it - you can't avoid loading all the pages if you get the Repeater field value.

  • Like 2

Share this post


Link to post
Share on other sites
6 hours ago, ratna said:

The code works and it iterates though all the people, but the sort order is not correct and seems to depend on the order of the repeater fields, rather than on the value

OK. I only tested with one repeater and the sort order worked (i.e. sorted by the values in the float). I'm still not getting your setup though. How many Categories can be selected per repeater item? A screenshot would also help. 

Share this post


Link to post
Share on other sites
10 hours ago, ratna said:

It's a very simple SQL query, so it seems to me this should be possible.


SELECT * FROM people LEFT JOIN categories ON (people.id = category.person_id AND category.type = 1) ORDER BY category.value DESC GROUP BY people.person;

 

Note that if you have an SQL query that you are finding difficult to turn into an equivalent selector then you do have the option of getting the page IDs via SQL and then loading those page IDs. An example (not that this is difficult to do as a selector)...

$sql = "SELECT pages.id FROM `pages` JOIN field_title AS field_title ON field_title.pages_id=pages.id AND (field_title.data LIKE '%a%') WHERE (pages.templates_id=59) AND (pages.status<1024)";
$query = $database->query($sql);
$ids = $query->fetchAll(\PDO::FETCH_COLUMN);
$items = $pages->getById($ids);

 

Share this post


Link to post
Share on other sites

Hello @ratna

if I understand your question, here is tested variant to get that.

All required templates (or admin page tree) are almost the same just like you done (or what @kongondo suggested), except that I used and "result" template.
Result template is child of "person" because for me it's somehow more nature to use "person" template to store only "person" details (eg. address, email etc...), and "result" to store "result" values per that user. Also, using "result" template can gives you flexibility to store and other result details (date, time, etc...).

Required templates and fields:

people: title
- person: title
-- result: title, category (page, select), value (float)

categories: title
- category: title

Here are few screenshots from backend, page tree (1)  and single result page (2):

page-tree.png.b3dab94d564c39db64e5068c5c9a5adb.pngresult-template.thumb.png.f9ad045bb75427d95506055a3dcb660e.png

*** Categories template is used to show "results" (frontend screenshot)

list-view-frontend.png.2732f8603b3d1bb9b370fc7d3badee9e.png

// categories.php
<!DOCTYPE html>
<html lang="en">
   <head>
      <meta http-equiv="content-type" content="text/html; charset=utf-8" />
      <title><?php echo $page->title;?></title>
      <link rel="stylesheet" type="text/css" href="<?php echo $config->urls->templates?>styles/main.css" />
   </head>
   <body>
      <h1><?php echo $page->title; ?></h1>
      <ul>
         <?php foreach($page->children as $category):?>
         <li>
            <?php echo $category->title;?>
            <?php $results = $pages->find("template=result, category=$category, sort=-value");?>
            <?php if(count($results)):?>
            <ul>
               <?php foreach($results as $result):?>
               <li><?php echo $result->parent->title;?> | <?php echo $result->value;?></li>
               <?php endforeach;?>
            </ul>
            <?php endif;?>
         </li>
         <?php endforeach;?>
      </ul>
   </body>
</html>

Regards.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.