Query of DataGrid depending on the value entered by the user

Support
#1

Hello, I have a complicated problem, that’s why I’ll start from the beginning.
I have the output tables “vssrvProgramBrickList” and in the edit/add new bricka view under the “Properties” button I want to add a grid.
image
When user enter program name, brick ID and brick type, and click Property I want to show that Grid below (I used DataGrid - not sure this is the best choise)
Into that grid I want to select from table vssrvbricktypepropertiesCfg propertyName and deafaultValue, which is added to datasource as CollectionDatasource and look like:
image
I want to show olny properties where the value “brickType” is the same which user choose above as Brick Type and propertyType = ‘PROP’
And here is my first problem how can I set Query of that dataGrid under button.
I tried to solve it this way:

public class VsSrvProgrambrickslistEdit extends AbstractEditor<VsSrvProgrambrickslist> {

    @Inject
    private DataGrid<VsSrvBrickstypepropertiescfg> typeProperties;

    @Inject
    private DataManager dataManager;

    public void onProperiesClick() {

        VsSrvProgrambrickslist record = getItem();
        String typeBrick = record.getBricktypeidS().getBricktypeidS();

        typeProperties.setVisible(true);
        typeProperties.getDatasource().setQuery("select p from pfvdm$VsSrvBrickstypepropertiescfg p where p.bricktypeidS.bricktypeidS =:typeBrick and p.propertytypeS = 'PROP'");

Here is my xml:
new 4.txt (2.4 KB)

I’m sorry for my english

#2

Hi,

The exception you posted caused by the incorrect parameter name in the datasource query. Parameters of datasource queries must have a prefix which specifies where to get the value. See Query Parameters. So in your case you can specify a parameter in the form :custom$typeBrick and then refresh the datasource with typeProperties.getDatasource().refresh(ParamsMap.of("typeBrick", typeBrick))

#3

Thank you wery much for your help last time. But angain I have problem with query.
This time I make a button to save brick properties to table VsSrvProgrambricksproplist. Everything works ok with saveing, but I need to check first if programID and brickID together are unique.

Here is my code:

 public void onSaveClick() throws ValidationException {
        VsSrvProgrambrickslist record = getItem();
        Collection<VsSrvBrickstypepropertiescfg> items = typeProperties.getDatasource().getItems();

        LoadContext<VsSrvProgrambrickslist> loadContext = LoadContext.create(VsSrvProgrambrickslist.class)
                .setQuery(LoadContext.createQuery("select p from pfvdm$VsSrvProgrambrickslist p where p.programIdI=:programID and p.brickIdS=:brickID")
                        .setParameter("programID", record.getProgramidI().getId())
                        .setParameter("brickID", record.getBrickidS()))
                .setView(View.LOCAL);

        long counter = dataManager.getCount(loadContext);

        if(counter>0){
            throw new ValidationException("Brick already exsits");
        }

        for(VsSrvBrickstypepropertiescfg item:items){
            VsSrvProgrambricksproplist newRecord = new VsSrvProgrambricksproplist();
            newRecord.setProgramidI(record.getProgramidI());
            newRecord.setBrickIdS(record.getBrickidS());
            newRecord.setBrickpropertyS(item.getPropertynameS());
            newRecord.setPropertyvalueS(item.getDefaultvalueS());
            programBricksPropListsDs.addItem(newRecord);
            programBricksPropListsDs.commit();
            windowActions.setVisible(true);
        }
    }
}

And here is my class VsSrvProgrambrickslist :

> @NamePattern("%s|id")

    @Listeners({"pfvdm_VsSrvProgrambrickslistEntityListener"})
    @Table(
        name = "vs_srv_programBricksList"
    )
    @Entity(
        name = "pfvdm$VsSrvProgrambrickslist"
    )
    public class VsSrvProgrambrickslist extends BaseIdentityIdEntity implements PersistenceWeaved, PersistenceObject, PersistenceWeavedFetchGroups, PersistenceWeavedLazy, PersistenceWeavedChangeTracking, CubaEnhanced {
        private static final long serialVersionUID = -9146266290557209528L;
        @ManyToOne(
            fetch = FetchType.LAZY,
            optional = false
        )
        @JoinColumn(
            name = "programId_i"
        )
        protected VsSrvProgramlist programidI;
        @NotNull
        @Column(
            name = "brickId_s",
            nullable = false,
            length = 100
        )
        protected String brickidS;
        @Column(
            name = "brickName",
            length = 100
        )
        protected String brickName;
        @ManyToOne(
            fetch = FetchType.LAZY,
            optional = false
        )
        @JoinColumn(
            name = "brickTypeId_s"
        )
        protected VsSrvBrickstypelistcfg bricktypeidS;
        @Composition
        @OneToMany(
            mappedBy = "programBrickId"
        )
        protected List<VsSrvProgrambricksproplist> prop;
        @Transient
        @XmlTransient
        protected WeavedAttributeValueHolderInterface _persistence_bricktypeidS_vh;
        @Transient
        @XmlTransient
        protected WeavedAttributeValueHolderInterface _persistence_programidI_vh;

        public VsSrvProgrambrickslist() {
        }

And here is my error:
image

I will be very grateful for your help

#4

I have solved my problem in this way:

String brick = record.getBrickidS();
    int program = record.getProgramidI().getId();
    LoadContext<VsSrvProgrambrickslist> loadContext = LoadContext.create(VsSrvProgrambrickslist.class)
            .setQuery(LoadContext.createQuery("select p from pfvdm$VsSrvProgrambrickslist p where p.programidI.id =:program and p.brickidS =:brick")
                    .setParameter("program", program)
                    .setParameter("brick", brick))
            .setView(View.LOCAL);
    System.out.println("**************************************************");
    long counter = dataManager.getCount(loadContext);