• 8
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

Unable to set count value to column

I am trying to fetch a component category that has a certain amount of components in a rack.

Query

@Query("SELECT id, name, component_amount = " +
        "       (SELECT COUNT(component.id) as amount " +
        "        FROM component " +
        "        WHERE component.rack_id = :rackID AND component_cat.id = component_cat_id ) " +
        "FROM component_cat")
LiveData<List<ComponentCat>> getRackComponentCategories(long rackID);

ComponentCat

public class ComponentCat
{
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    public long componentCatID;

    @ColumnInfo(name = "name")
    private String componentCatName;

    @ColumnInfo(name = "component_amount")
    private int amountOfComponents;


    public ComponentCat(String componentCatName, int amountOfComponents) {
        this.componentCatName = componentCatName;
        this.amountOfComponents = amountOfComponents;
    }

Testing this in PHPMyAdmin it works, but putting this SQL in my Room application, I get the following error:

  error: The columns returned by the query does not have the fields [amountOfComponents] in com.mwb.digitalstorage.model.ComponentCat even though they are annotated as non-null or primitive. Columns returned by the query: [id,name,component_amount =        (SELECT COUNT(component.id) as amount         FROM component         WHERE component.rack_id = :rackID AND component_cat.id = component_cat_id )]

In Room, how do I set the variable amountOfComponents to the COUNT() result?

I have tried:

  • Having a COUNT(component.id) as component_amount, but such does not give the right outcome
  • Re-creating the table in MySQL and perform the same query, which did give the right outcome
  • Removing @NonNull at the fields, no difference
  • assigning the component_amount.amount

    @Query("SELECT id, name, component_amount.amount = " + " (SELECT COUNT(component.id) as amount " ...

    this does not build

Room does not support this type of giving an alias to your columns.

Your error Message states :

Columns returned by the query: [id,name,component_amount =        (SELECT COUNT(component.id) as amount         FROM component         WHERE component.rack_id = :rackID AND component_cat.id = component_cat_id )

Which means it is interpreting the whole query as a column.

Try changing your Query to the following:

@Query("SELECT id, name, " +
        "       (SELECT COUNT(component.id) as amount " +
        "        FROM component " +
        "        WHERE component.rack_id = :rackID AND component_cat.id = component_cat_id ) as component_amount " +
        "FROM component_cat")
LiveData<List<ComponentCat>> getRackComponentCategories(long rackID);
  • 1
Reply Report
      • 1
    • "Which means it is interpreting the whole query as a column" that is what I am aiming for, the outcome of that query assigned to the column. It works in PHPMyAdmin, but didn't at Room. Your answer does work in Room. Thank you