Make a generated column sortable?

Hi,

I use the Table component together with a BeanItemContainer as datasource. Some of the Bean properties are numbers and have a special meaning. I want to show the textual meaning of these properties and not the numbers itself to the user. Therefore, I use several generated columns which do what I want, except one thing: these generates columns are not sortable.

Is there something I can do to make such columns sortable?

Here is a snippet of the column generation:

    table.addGeneratedColumn("Type", new Table.ColumnGenerator()
    {
      @Override
      public Component generateCell(Table source, Object itemId, Object columnId)
      {
        Component comp = null;
        if (itemId instanceof InfoBean)
        {
          InfoBean ib = (InfoBean) itemId;
          comp = new Label(Helper.readableType(ib.getType()));
        }
        return comp;
      }
    });

Hi,

Basically: no. Since the columns are generated on the fly, and only for the rows that are rendered on the client - other values “don’t exist”, and values could change since they are being re-generated the next time they are sent to the client.

I’d recommend using a customized component for those columns, or overriding Table.formatPropertyValue().

Best Regards,
Marc

However, if you really want to sort generated columns, two alternatives: make the generated column “alias” (with the same property name) and so hide a sortable property that is present in the container, or override sort() in your container and handle any custom sorting there. Both approaches rely on the container to do the sorting, not the table and its column generator.

You might also need to override Table.getSortableContainerPropertyIds() or the corresponding method in your container, and you probably need to re-generate all the generated values when sorting (possibly many times depending on your implementation).

I’ve done it as Henri suggested: name your generated column to exactly the same as the real column, if one is available. No need to override any other features like sorting.

However, if it is actually is only text that you show with the generated column, then do it rather with Table.formatPropertyValue(Object, Object, Property) as that is very much faster for the browser to render, than labels from a generated column.

Hi Henri,

I tried your “alias” approach and that works for my case perfectly. Doing it this way, I can also sort my column that contains a link!

Thank you all for your replies

Andreas

I use a third approach. I use a CellSyleGenerator which I add to the table. In this examble I use a different cell style for an active/inactive legalEntity

public class PrsLegalEntityCellStyleGenerator implements Table.CellStyleGenerator{
public String getStyle(Object item, Object propertyId) {
if (PrsLegalEntityBeanItemContainer.PROPERTY_ACTIVE.equals(propertyId)) {
K legalEntity = (K)item;
if (legalEntity.isActive()) {
return “active”;
} else {
return “inactive”;
}
} else {
return null;
}
}
}

In the styles.css I put font-size 0 (text will be invisible) and I put an image

.v-table-cell-content-
active
{
font-size : 0px;
background-image: url(“…/prs/images/status_A.gif”);
background-repeat: no-repeat;
}

.v-table-cell-content-
inactive
{
font-size : 0px;
background-image: url(“…/prs/images/status_I.gif”);
background-repeat: no-repeat;
}

I’m sorry, but I don’t think I understand what you were recommending…

I have a table with one column being generated (actually there are 3 or 4 generated columns in this table, but for now one is all I need to worry about). The data object has a foreign key reference to another data object and I’m trying to display the object name. That works, but I can’t sort the table. I tried changing the column name to be the same as the foreign key id, but that doesn’t do anything. I assume I’m not understanding what is required.

Here is part of my code:


                tbl_Ssp = new Table();
		tbl_Ssp.setHeight("100%");
		tbl_Ssp.setWidth("1100px");
		tbl_Ssp.setSelectable(true);
		tbl_Ssp.setImmediate(true);

		tbl_Ssp.addGeneratedColumn("vtcInfo", new Table.ColumnGenerator() {
			private static final long serialVersionUID = 1L;

			@Override
			public Object generateCell(Table source, Object itemId, Object columnId) {
				Label label = null;
				VtcInfo vi = (VtcInfo) source.getItem(itemId).getItemProperty("vtcInfo").getValue();
				label = new Label(vi.getVtcName());
				return(label);
			}
		});

This picks up the name string and displays it in the table.
I’d like to be able to sort on the contents of this column. Can someone point me at some sample code that would allow me to do this?

thanks,

nbc

The container is responsible for sorting. Then the table displays information, and computes the generated columns. This is why you can’t sort the generated columns. If you have a small dataset, you may want to create a bean item container or Pojo container and add accessor methods to compute the values (getSomeComputedValue()) and add someComputedValue as a property. That way the container will sort the values – just be aware that this may result in your get accessors being computed several times, depending on the container.

Sorting works with the generated column name the same as the original data field! thanks!
in my case, my generated column is based on a existing column say “id” and make it a link. I was using a different column name for the generated column and noticed it is not sortable anymore. Now the issue is resolved.

Hi Henri,

I tried the approach of created generated column with same name as the real attribute of the bean/POJO. This approach worked with BeanItemContainer where the column name was an instance field in the POJO. But this approach is not working with Indexed Container. Can you please suggest some solution? Thanks in advance:)

Make sure the property is returned from your indexedContainer.getSortableContainerPropertyIds().

If not, make sure the type of the property is correct (something implementing Comparable or a primitive type) - see your calls to addContainerProperty(…).

If necessary, you can also override AbstractInMemoryContainer.getSortablePropertyIds() for your container if the actual data in the column is safely comparable or if you are using a custom ItemSorter that can handle the data.

Could you please give an example what is ment by “make your column alias”??

Just use the same name for the generated column that is the property name in the container.

Probably I’m doing things wrong, but the following does not work:


		table.addContainerProperty("amount", Integer.class, null);
		table.addGeneratedColumn("amount", new ColumnGenerator() {
			@Override
			public Object generateCell(Table source, Object itemId, Object columnId) {
				return 123;
			}
		});

Still it is not sortable.

Sorting is based on what is in the container, not what the generator returns.

In order to support lazy loading, the framework cannot call the generator for every row (there could be 100000 rows in the container) and thus it is not possible to sort by those values in the generic case.

Hm, so just to make it clear: it is NOT possible to sort values of a generated column, when these values are calculated on the fly depending on other column values, right?

Absolutely, 100%, for definte: As Henri says, sorting is based on the container and items. That’s another reason why using generated columns to generate values is not a good approach.

Look at it like this : table#setColumnGenerator is used if you want to generate something other than a simple DIV/Label for a value (e.g. a tick/cross image, or a pseudo column containing buttons).

If you want to calculate a value, it should sit on the item (or bean, if you’re using a BeanItem).

Looked at with this perspective, it makes sense - imagine you have a table of 100,000 rows (which the Table component supports). You really don’t want to have to render every row to sort it. (Vaadin only calls the column generator for visible rows, + a small buffer around them) => typically, you’ll only have 30-40 generated components at a time.

I hope that adds a little extra clarity.

Cheers,

Charles.

So which approach is the best if the generated column is based on a POJO? Let’s say I have a table which displays companies including the name of one out of many employees attached to the company. (Using BeanItemContainer!)

Until now, I’m using an IndexedContainer. But I’d like to use a BeanItemContainer for less complexity.

Hi Henri,

containers have String, Double and Integer values. but still sorting dont work when clicking on column header. but it show toggling of small up and down arrow after clicking on header.

Upon searching in a Foreign key referenced Generated Column, I get this error after type one (or more) characters:

Caused by: Exception [EclipseLink-4002]
 (Eclipse Persistence Services - 2.6.1.v20150605-31e8258): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Incorrect parameter count in the call to native function 'UPPER'
Error Code: 1582
Call: SELECT COUNT(t0.device_id) FROM argdb.device_list t0, argdb.device_type t1 WHERE (UPPER() LIKE UPPER(?) AND (t1.device_type = t0.device_type))
    bind => [1 parameter bound]

Query: ReportQuery(referenceClass=Device sql="SELECT COUNT(t0.device_id) FROM argdb.device_list t0, argdb.device_type t1 WHERE (UPPER() LIKE UPPER(?) AND (t1.device_type = t0.device_type))")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2055)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:258)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2740)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2677)
    at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:852)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1134)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:460)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1222)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
    ... 51 more

The first Upper() has no parameter bound? Why would this happen?
I set the column name the same as the actual property, as described above.
It fixed the sorting on a Fontawesome Label column easy peasy, but the foreign key column gives this error.