Joins between Entities
Question

Hello.

I'm trying to fill a table with info about my machines.

My Entities Diagram:

My machines Table: 

Machines OB1989 and OB1621 does not have options assigned. OB1417 have two options assigned.


My Aggregate Table:



What am I doing wrong?

Already tried Group By on Serial Number but I always have two lines for that machine number.

Solution

Thanks.

I've just used this code:

SELECT * 


FROM (    
    SELECT {Options}.[Name] as OptionName,
            {MachineOption}.[OptionActive] as IsActive,
            {Machine}.[SNumber] as MachineSN
         
    FROM {MachineOption}
    INNER JOIN {Options} ON {Options}.[Id] = {MachineOption}.[OptionId]
    INNER JOIN {Machine} ON {Machine}.[Id] = {MachineOption}.[MachineId] 
    GROUP BY {Options}.[Name], {MachineOption}.[OptionActive], {Machine}.[SNumber]
        
    
) R

Pivot (max(IsActive) for OptionName in ([OP 1],[OP 2],[OP 3],[OP 4],[OP 5],[OP 6],[OP 7],[OP 8])) as Columns Order by 1


After that I saw your answer.

My goal now is to use a String Parameter as Input and then use it for the columns name:


 Pivot (max(IsActive) for OptionName in (STRINGIN @StringIn)) as Columns Order by 1 

I've already have the correct string, but having a hard time to define my Output Structure.

With the code above I get the following error:

Ok,

i'm not really getting what you are doing with that, and can't really see my way into fixing, but I think you ame at having option 1 to 10 as column, and these values filled with the option names.  I think that would not lead to neatly aligned option names, though, but maybe that's not what you are after.

When I'm looking at the screenprint in your original post, I think you could use something like this :

SELECT MachineSN, xxxxx, yyyyyy, zzzzzz
FROM (
    SELECT {Machine}.[SerialNumber] as MachineSN, {Option}.[Name] as OptionName, {MachineOption}.[Id] MOID
    FROM {Machine}
    JOIN {MachineOption} ON {Machine}.[Id] = {MachineOption}.[MachineId]
    INNER JOIN {Option} ON {MachineOption}.[OptionId] = {Option}.[Id]     
) temp
PIVOT(
    count(temp.[MOID])
    for temp.[OptionName] IN([xxxxx],[yyyyyy],[zzzzzz])
) AS pivot_table

In this, xxxxx, yyyyyy and zzzzzz are names of options.  This is now hardcoded, but you could work with input variables that are flexibly filled with the real option names retrieved from your actual data.

It gives this result set, aaaaaa and bbbbbbb are the 2 machines in my test database, you can translate the 0/1 values to boolean

Hello @SirT, what is happening? what is the real error? from what I am seeing, is working ok, because just OB1417  has options selected, either on the screen or the aggregates. Can you explain?

Regards,

Márcio C.

Hello @SirT,

Just based on what I see, I would recommend:

1. Creating a Foreign Key for MachineOption in the Machine table (I assume here that 1 option maybe available to many machines?) 

2. Combining MachineOption and Options tables so that MachineOption would include the Name attribute. (Again I assume a few things here but I am not seeing a reason to keep these entities separate.)

3. With the above changes, you would still have 2 rows for OB1417, but you would just need a single column to display Option (which would be {MachineOption}.[Name] per above recommendations). So if a Machine has 4 options, you will expect to see 4 rows in your table but at least you will avoid having to display 4 columns.

4. If you still must see all your options for a Machine in a single row then you may want to 'pivot' your results and while I have not done that in OutSystems, maybe forge components such as this may help.

Hope this helps a bit,

Regards,

AJ

Champion

Hi 

It looks like normal behavior. Can you provide more detailed information? 

What are the expressions for Option1 and Option2?

Regards,

Hello SirT,

Could you please change the join#6 from "With or Without" to "Only with" and give a try? Based on the screenshots, it looks like between Option and MachineOption entities a Cartesian join have been created and it is showing all the rows of MachineOptions with or without the Option Identifier.

Thanks & Kind Regards,

Sachin

Hi SirT,

from your post I gather that you are looking to flatten your 1 to many relationship between Machine and MachineOption into a single row in your screen per machine, is that right ?

So, first off, it looks like you defined 2 columns "option1" and "option2", is this hard design ?  In other words, what if new options are added to your database, what will be the effect on your screen ?  Are you only ever interested in options 1 and 2, or do you want option 3 as an extra column in your screen table if it gets added to the database ?

There are several approaches to this, but we need more clarity on exactly what you want.  

One thing you could do is nest a webblock displaying all the options of a given machine, into a cell of your machine table.  Either have each webblock fetch the options for that particular machine, which would make for a lot of traffic, or do one big join for the machine table and then pass the related options into the webblock, avoiding multiple separate round trips to the server.

Your challenge will be that this options block is not the same as having each option in a real separate column of the table, so aligning with the header might become a problem if there are too many options.

A more 'techy' approach would be to use an SQL widget instead of an aggregate, and PIVOT the options into a single row per machine.  But here, I'm not sure how well that would play together with table and list widgets.

Dorine

I made a little demo,

as you can see, option 1 where each webblock fetches what it needs, is far simpler than option 2.  So if there are no performance considerations and/or for beginner developers, I'd go with that.



Dorine

QDRShowOptions.oml


Correction : the aggregate in the webblock turns out to be a bit more complex, as you don't want a simple join, but also want to show options that are not related to the current machine.  

Also added option to check uncheck in the list.


QDRShowOptions.oml

Hello.

First of all thanks for all the replies. To explain the options part:

I have an entity called Machines. To each Machine I will assign one SerialNumber, one Model, one Screw Size and up to 16 Options. I also have dates, display and schematic status but that's not relevant for the question.

I can create different Models, Screws and Options.

The columns Option 1, Option 2, Option 3... Option 16, will be created by hard design. So each machine row, will show check and unckeck at the corresponding option.

I did it like that because I didn't want Machine to have 20 attributes...


@Marcio Carvalho I only want three lines. One for OB1989, another for OB1621 and another for OB1417 but I'm guessing I can't do it like this. One machine can have many options.


@=AJ= 

  1. Correct - One option can be assingned to many machines.
  2. I have done it like this because I didn't want 16 more attributes always inside Machine Entity. It feels too much.
  3. I always want to see the 16 columns so I can easily see the check/uncheck and later filter by options.
  4. I will have a look.


@Tom Zhao is this reply helpfull? 

My code for Option 1 and Option 2 right now is a little big ugly and does not feel professional:

(GetMachines.List.Current.Options.Name = "Option1") and (GetMachines.List.Current.MachineOption.OptionActive = True)


Sachin Waghmare

Changing it from "With or Without" to "Only with"  will not show my two machines without options, OB1989 and OB1621. It's possible that a machine does not need option.


@Dorine Boudry 

Yes I want all 16 options shown in screen with check or uncheck.



So I've dediced to go with the PIVOT solution.

My SQL Query:

SELECT MachineSN ,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]


FROM (    
    SELECT {Machine}.[SNumber] as MachineSN, 
        {Options}.[Name] as OptionName,
        'Option'+cast(row_number()over(PARTITION by {Machine}.[Id] order by {Options}.[Id]) as varchar(10)) seq
         
    FROM {Machine}
    INNER JOIN {MachineOption} ON {Machine}.[Id] = {MachineOption}.[MachineId]
    INNER JOIN {Options} ON {MachineOption}.[OptionId] = {Options}.[Id]  
        
    
) R
Pivot (max(R.OptionName) for seq in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10]) ) as Columns

My Output:

Both machines have multiple options. With this query I've solved the multiple lines per option.

Right now I'm only missing the options name value in each cell.

What am I doing wrong?


Ok,

i'm not really getting what you are doing with that, and can't really see my way into fixing, but I think you ame at having option 1 to 10 as column, and these values filled with the option names.  I think that would not lead to neatly aligned option names, though, but maybe that's not what you are after.

When I'm looking at the screenprint in your original post, I think you could use something like this :

SELECT MachineSN, xxxxx, yyyyyy, zzzzzz
FROM (
    SELECT {Machine}.[SerialNumber] as MachineSN, {Option}.[Name] as OptionName, {MachineOption}.[Id] MOID
    FROM {Machine}
    JOIN {MachineOption} ON {Machine}.[Id] = {MachineOption}.[MachineId]
    INNER JOIN {Option} ON {MachineOption}.[OptionId] = {Option}.[Id]     
) temp
PIVOT(
    count(temp.[MOID])
    for temp.[OptionName] IN([xxxxx],[yyyyyy],[zzzzzz])
) AS pivot_table

In this, xxxxx, yyyyyy and zzzzzz are names of options.  This is now hardcoded, but you could work with input variables that are flexibly filled with the real option names retrieved from your actual data.

It gives this result set, aaaaaa and bbbbbbb are the 2 machines in my test database, you can translate the 0/1 values to boolean

Solution

Thanks.

I've just used this code:

SELECT * 


FROM (    
    SELECT {Options}.[Name] as OptionName,
            {MachineOption}.[OptionActive] as IsActive,
            {Machine}.[SNumber] as MachineSN
         
    FROM {MachineOption}
    INNER JOIN {Options} ON {Options}.[Id] = {MachineOption}.[OptionId]
    INNER JOIN {Machine} ON {Machine}.[Id] = {MachineOption}.[MachineId] 
    GROUP BY {Options}.[Name], {MachineOption}.[OptionActive], {Machine}.[SNumber]
        
    
) R

Pivot (max(IsActive) for OptionName in ([OP 1],[OP 2],[OP 3],[OP 4],[OP 5],[OP 6],[OP 7],[OP 8])) as Columns Order by 1


After that I saw your answer.

My goal now is to use a String Parameter as Input and then use it for the columns name:


 Pivot (max(IsActive) for OptionName in (STRINGIN @StringIn)) as Columns Order by 1 

I've already have the correct string, but having a hard time to define my Output Structure.

With the code above I get the following error:

ha ha, yes.  

I avoided all that messyness by using FOR JSON.

I don't think the sql widget is very flexible about format an number of output fields, so I don't think it works with an attribute that is a list???

So if you want to be flexible with the number and names of options, you'll have to find a solution for that.

Thanks for the help.

I will try it using JSON.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.