SELECT DISTINCT category FROM movies (with PW API)

[EyeDentify]

Hello Again friends.

I was working on a new website and i had a mental block about a query i would like to do with the help of PW API.

And then i could not figure out how to do this specific one.

I know how to do it in MySQL for example:
 

SELECT DISTINCT category FROM movies

The purpose here is to get all the uniuqe categorys from those pages that are each one a movie title.
Because there could be the same category string in more then one movie title.

For example say:
1. Die Hard (category: Action)
2. Die Hard 2 (category: Action)
3. Die Hard 3 (category: Action)
4. Ghosts of Mars (category: Sci-fi)

Then i would like to at the end of the query get back:

1. Action
2. Sci-Fi

As uniqe categorys.

And then make category links using those uniqe categorys wich is no problem either.

But how do i perform the above MySQL query in PW API terms ?

I Have gone through all Selector documentation and havent found something that directly represent what i want to do.

And i know i could use the SQL class and methods, but i want to use PW API as much as possible to learn.

I would like to not have to get all the movie title pages with regular PW API and then with some Array magic sort out the uniqe categorys

if i could avoid it, cause i know this site will have alot of movies and tv series in the "database" in the future.
So that could mean alot of memory usage.

Or is my best bet to just have a "tree" structure of say:

movies
- Action

-- Die Hard 1
-- Die Hard 2
-- Die Hard 3
- Sci-Fi

-- Ghost of Mars

And then say, use a text field on each title page that has the type of either "movie" or "tv series" for example ?
Because i want to be able to sort distinguish between them later on.

If anything is confusing about my question, just ask and i will try to clarify.

[LostKobrakai]

I think those might be what you're looking for:

https://processwire.com/talk/topic/9730-get-pages-used-by-a-pagefield/

http://cheatsheet.processwire.com/pagearray-wirearray/getting-items/a-unique/

[EyeDentify]

I think those might be what you're looking for:

https://processwire.com/talk/topic/9730-get-pages-used-by-a-pagefield/

http://cheatsheet.processwire.com/pagearray-wirearray/getting-items/a-unique/

Thank you for the post LostKobrakai

The link about unique looks promising.

But i am still going to first have to return all the records in the PageArray and then filter them with unique ?

Is that not expensive when i have alot of pages in that array ?

[LostKobrakai]

If you're worried about that just use the mysql snippet ryan posted with the DISTINCT selector. 

[EyeDentify]

If you're worried about that just use the mysql snippet ryan posted with the DISTINCT selector. 

Thx again.

I will have a look at Ryans solution.

It seems pretty straight forward to change for my needs.

Though i was still curious if there is a strictly PW API way of doing this.

if i could specify that i only want to return the "category" field from the pages i target with a selector and that would make it less expensive if i have alot of pages ?

If not then i will look at Ryans code and come up with something on my own.

I am not that worried about performence right know, but down the line maybe.

Thanks again for all suggestions.

[LostKobrakai]

That's why this is a wishlist topic. There's no pw way for that besides loading all pages to memory (kinda manually) and using unique().

[EyeDentify]

That's why this is a wishlist topic. There's no pw way for that besides loading all pages to memory (kinda manually) and using unique().

Oki Then i have my answer.

I will have to rethink my solution and look at the referenced Ryan Solution wich seems good for my case.

Thx  Again LostKobrakai.