Topic: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

So this whole idea started when I was messing with the SQL Tables for the first time in QET. I decided I wanted the Title Page of my project to be page 0 and not shown in a Table of Contents.

I originally had issues because Folio number shows the folio # / Total # of Folios. I wanted to only show the folio # and manipulate it. What I came up with was

SELECT pos-1 AS Page, title FROM project_summary_view ORDER BY pos LIMIT 26 OFFSET 1

https://autumn.revolt.chat/attachments/_J47BQ9GEhrjTM6xOH6w3ptCQFI0HO-oE-ddV_xHt0/screenshot0001.png

Found out pos is my folio # and the -1 allows me to modify the #. The OFFSET 1 allows me not to show page 0 in the Table of Contents as this is my Title page. However, I want to expand upon this but running into a couple of issues with doing so.

The first issue is the Title Block. %folio-num for the Page: number displays at folio/folio-total and adding a -1 just adds the -1 as a text. Example: 2/26-1. I was able to get around this with %previous-folio-num which works for the above specific example. Another smaller issue is that the page total is off by 1. I wasn't planning on figuring that out since it wasn't that big of a deal. However, more issues if I expand upon the idea.

The second issue is with multiple SELECT statements. I thought I could do something like SELECT "" || "I." AS Page, title from project_summary_view ORDER BY pos LIMIT 1 OFFSET 1; SELECT pos-2 AS Page, title from project_summary_view ORDER BY pos LIMIT 26 OFFSET 2

Was trying to get the Table of Contents to show up as I. and Folio 3 is Page 1. The above statement does not work. Even if it did, I will have further problems with the title blocks as I will at the very least need to make the folio # in the title block match my Page # in the Summary Table.

2 Reply by FoxTech (edited by FoxTech 2023-12-19 12:33:53)

Re: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

I actually got really close with the following statement

SELECT "" || "I." AS Page, title from project_summary_view WHERE title = "Table of Contents" UNION SELECT pos-2, title from project_summary_view LIMIT 26 OFFSET 2

This actually returns all the rows I want, but I can't use the ORDER BY function or else it fails to return any results. The actual result without ORDER BY is very close to what I was attempting to do but it is showing the Table of Contents at the bottom. I also will have additional issues as stated above with the title blocks even if I solve this.

https://www.sqlite.org/lang_select.html#simpleselect

Section 3 Compound Select Statements, almost 3/4 of the way down that page it shows the ORDER BY being used with UNION. However, this is breaking my tables. nomicons/sad

Re: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

I am actually going to abandon this idea all together. I do not believe this is a problem on the side of the QET team but more to do with limitations of SQLite. Trying to do compound SELECT statements while introducing math seems to break it if you do an ORDER BY. Which is strange, because ORDER BY shouldn't make a difference since ORDER BY is done to the unmodified values, not the ones I am modifying.

I have tried getting around this by using SELECT REPLACE("pos", value, value) and several other work arounds but they all break down when introducing ORDER BY. Even if I got it to work, I predict some other issues such as OFFSET modifies your resulting output and I can't see a way through SQLite to modify each table before you combine them.

Additionally, even if I found a work around, I can't make changes to QET variables. as far as I can tell, the custom variables you are allowed to set for headers don't allow you to introduce any sort of math which I would need to do with what I am attempting here.

The idea here was that for in a much larger project, I could number the Table of Contents separately with roman numerals and start page 1 with the actual schematic. Although it is close to being possible, I don't see a path forward to the finish line here.

If anyone else wants to expand upon the idea or knows of any solutions, it would be appreciated. nomicons/smile

  • scorpio810
  • scorpio810
  • QElectroTech Team Manager, Developer, Packager
  • Offline

Re: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

In principle a project has a cover page on the first folio, see provided example : projet_vierge.qet.

"Le jour où tu découvres le Libre, tu sais que tu ne pourras jamais plus revenir en arrière..."

5 Reply by FoxTech (edited by FoxTech 2023-12-22 08:53:10)

Re: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

https://autumn.revolt.chat/attachments/VhRkyMxr3FrOpOesltj5gUhQFwnTZmvEVpKXWWzJAP

In essence, this is what I was attempting to do. The Title page exists, but not in the Summary and not counted as a folio/page. Additionally, the roman numerals for Table of Contents and folio/page 1 being the actual schematic.

1. SQLite limitations are prohibiting me from making a compound select statement. An error exists when trying to incorporate math and the ORDER BY operator when in a COMPOUND SELECT query with the UNION operator. In fact this error is persistent when using UNION and ORDER BY with various other operators such as REPLACE().

2. Even if I successfully create the SQLite Query that I want, OFFSET is used to OFFSET the table output, as far as I can tell, there is no way to OFFSET the tables before you create the UNION. This means even if I get my roman numerals to appear at the top of the table, I will OFFSET them away when I want to OFFSET other parts of the table.

3. I can't figure out a way to get rid of the Table Header, so I can't even try faking it by creating more than 1 table and just have one sit on top of the other, creating the illusion of 1 table.

4. Now, I could create separate tables and keep the headers and try to build that into the aesthetic of the Summary design. I might write a query for a Table of Contents Table of more than 1 pages something like,

SELECT "I." AS Page, title FROM element_nomenclature_view WHERE pos =  '1' UNION SELECT "II." AS Page, title FROM element_nomenclature_view WHERE pos = '2'

I assume the above query would work, but in order to create the custom width columns by creating individual tables for each column, I would have to just do the above for title only, and manually create the boxes as drawings for the Page column. As an error is created if I don't pull from any specified column.

I could do something really dumb like

SELECT "I,." || date AS Page, FROM element_nomenclature_view WHERE pos = '1' UNION SELECT "II." || date AS Page, title FROM element_nomenclature_view WHERE pos = '2"

However, I would have to leave the date for the table of contents blank

5. Even if, I some how resolve all the issues above. QET does not allow me to modify the custom variables like %{folio-id} with math. Example where %{folio} = 1/30, (%{folio-id}-1) = (1-1) . Here I would be trying to get a result of 0. ((((!!!!!! If there is a way to do this, please please please let me know!!!!!!!!))))))

#5 kills the whole idea. Without a way to modify QET Custom Variables, there is no point in trying to work through the SQLite issues. My Title Blocks on each page will be incorrect even if I solve the summary issues.

  • scorpio810
  • scorpio810
  • QElectroTech Team Manager, Developer, Packager
  • Offline

Re: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

https://qelectrotech.org/forum/viewtopi … 153#p16153

"Le jour où tu découvres le Libre, tu sais que tu ne pourras jamais plus revenir en arrière..."

Re: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

OMG Thank You SCORPIO EIGHT ONE ZERO!!!!!!

https://autumn.revolt.chat/attachments/9yoEfZubZqJggSuH7lHwaVXtvcREUVIpRDj24GMGiV

Used two tables to give the illusion of one table.

SELECT folio AS Page FROM project_summary_view ORDER BY pos LIMIT 26 OFFSET 1

and

SELECT title FROM project_summary_view ORDER BY pos LIMIT 26 OFFSET 1

Didn't have to mess around with the broken UNION operator any more. Prefect nomicons/w00t

  • scorpio810
  • scorpio810
  • QElectroTech Team Manager, Developer, Packager
  • Offline

Re: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

I't s only a little search on forum... nomicons/angel

Great!! nomicons/wink

"Le jour où tu découvres le Libre, tu sais que tu ne pourras jamais plus revenir en arrière..."

Re: Folios/Pages starting at 0 in Summary Table, Compound SELECT, +more

I had found the post before and the correct wiki page a few times. But I had trouble understanding...  i looked  at it much more closely when you linked with the  reply. I still had to play around with it before I finally understood how it was supposed to work. Seems simple now, but before, i had yet to even understand the two folio numbering tabs were related.