HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

Moving forward with Composr

ocPortal has been relaunched as Composr CMS, which is now in beta. ocPortal 9 will be superseded by Composr 10.

Head over to compo.sr for our new site, and to our migration roadmap. Existing ocPortal member accounts have been mirrored.


How to get category/sub-category in query?

Login / Search

 [ Join | More ]
 Add topic 
Posted
Rating:
#110849 (In Topic #22270)
TQ
Avatar

Honoured member

Hi all,

I'm sorry to ask such a dumb question but I've visited this many times and just can't get my head around it.

I want to publish a table that includes all the downloads I haven't yet validated. The table would includes the title (text_original), author and categories (or just sub-category).

My catalogue looks like this:

Code

Downloads home> Manuals> Alinco> ALINCO DJ G5 Service manual

Downloads home> Military Radio> Collins/Rockwell PRC-515 (RU-20)> Collins PRC-515 RU20 Manual (English)

Downloads home> Radio Programming Software & Data> Motorola> APXFAMILY CPS R11.0.0
Using the last line as an example; I don't need 'Downloads Home' and could manage without 'Radio Programming Software & Data' if I had to but would like at the very least 'Motorola' (sub-category) and 'APXFAMILY CPS R11.0.0' (text_original).

This gives me the Title & Author:

Code

SELECT text_original, author
FROM ocp_translate t
LEFT JOIN ocp_download_downloads d
ON t.id = d.name
WHERE validated = 0
order by text_original asc

I assumed I could complete this task with a 3 table query but, for the life of me, I can't work out where the category data is stored.

Any help would be very much appreciated.

TQ
Back to the top
 
Posted
Rating:
#110851
Avatar

Hi,

download_downloads.category_id is a foreign key to download_categories.id.

download_categories.parent_id is a foreign key back to download_categories.id, to create the tree structure.

download_categories.category is a foreign key into translate.id.

Try this:

Code

SELECT
t_title.text_original AS title,
t_category.text_original AS category,
t_higher_category.text_original AS higher_category,
download.author

FROM ocp_download_downloads download
LEFT JOIN  ocp_translate t_title ON download.name = t_title.id
LEFT JOIN  ocp_download_categories category ON download.category_id = category.id
LEFT JOIN  ocp_translate t_category ON category.category = t_category.id
LEFT JOIN  ocp_download_categories higher_category ON category.parent_id = higher_category.id
LEFT JOIN  ocp_translate t_higher_category ON higher_category.category = t_higher_category.id
WHERE validated = 0
ORDER BY title

Btw, with v10 we are dropping use of the translate table by default, which simplifies things a lot.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#110862
TQ
Avatar

Honoured member

Chris, that is perfect.

I still have so much to learn. Although I've read numerous descriptions of foreign keys, their use doesn't come naturally to me and I've been unable to work out how to follow them in HeidiSQL.

With regard to V10, is it going to be possible to port over my V9 site or is it going to be a fresh start?

Thanks again, I really appreciate your time.

TQ
Back to the top
 
Posted
Rating:
#110863
Avatar

There'll be an upgrader, very similar to upgrading from any other release, just with a longer set of notes ;).


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#110864
Avatar

Oh and specifically regarding the translate table…

If upgrading v9 it will still use it. You would need to run a separate script to turn off multi language content, which is explained in the docs.

Fresh installs won't have multi language content enabled by default.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#110865
TQ
Avatar

Honoured member

Sounds like it's time to start looking at v10.

Thanks again

TQ
Back to the top
 
1 guests and 0 members have just viewed this: None
Control functions:

Quick reply   Contract

Your name:
Your message: