Display a single result Query

I'm running a query in which I only expect one row to match the WHERE clauses. I want to display this as a "card" in my perspective view. *I'm using DISTI|NCT in the SELECT clause to help clean up the result.

What would be the best way to display this information? I thought about FlexRepeater, but I don't think that's the tool for the job here.

Issue 1: Is there a better way to make sure I only get 1 row for each column?

Issue 2: How can I convert the result to an array so it will play nice with the components?

Depending on your DB flavor, if the WHERE clause isn't specific enough to isolate a single row, then you can limit the number or rows returned.

SELECT TOP 1 * FROM TABLE

or

SELECT * FROM TABLE LIMIT 1

I think that depends on how you're running the query.

Are you expecting 1 row returned? Or maybe 0 or 1 rows returned?

What do you want to happen if there are actually multiple matching rows? Should it return an error, use the oldest one, use the newest one, use a random one, ...

Are you calling this from a script? If you are calling from a script and there will never be "too many" records even in the worst case (a few dozen or hundred) then just do a SELECT * FROM table WHERE filter. Use your script to pick your desired action if there is more than one record returned.

If you want to enforce return 0 or 1 rows in the database (not calling from a script, potential to return millions of rows, etc.) then you will need to check with the database you are using. MySQL will be different than SQL Server which will be different than Oracle. In SQL Server you would write SELECT TOP 1 * FROM table WHERE filter

You might be able to redesign the table and the filter so that it would be impossible to return more than row (with a Unique Constraint for example).

TOP 1 works.

I tried a scalar query but it only gives 1 result for the first column and no other columns. Which is what I think I should be expecting.

Converting that to an array is where I'm stuck.

I'm trying to convert a handwritten worksheet that the user looks up the info and writes it down then transfers it to a spreadsheet. So trying to automate that task.

I'm looking up a row by a serial #, so I would expect 0 or 1 row to return.

There shouldn't be multiple matching rows the way the database is set up, there would be a conflict in the serial #.

No, there is an input field for the serial # that sets a custom prop and the query uses that as a WHERE filter.

If it is impossible for the query to return more than one record in the result then you don't need to protect it with a TOP 1 or DISTINCT at all.

How do I convert the result to an array?

Where are you calling the query?

It would be bound to some sort of display component (not sure what would work best).

I basically want a vertical list of the columns with the result next to it.

edit: If I were doing this in something other than Ignition, It would be two columns, one for the column name and another for each result.

Am I overthinking this? Basically a form is what I'm trying to create.

Add transform: rotate(90deg) to your table's styles.
...

...
Just kidding.
Bind your query to a table, then add a script transform that would look like this:

def transform(self, value, quality, timestamp):
	columns = [value.getColumnAsList(col_index) for col_index in xrange(value.columnCount)]
	return [
		{
			'name': col[0],
			'value': col[1]
		} for col in columns
	]

This may be exactly what I need. I don't know anything about scripting.

I'm getting an error:

SyntaxError: ("no viable alternative at input 'index'",('', 5, 30, '\t [value.getValueAt(1, col)index) for col)index in ]\n'))

I think there's an open parenthesis missing in there somewhere.

I fixed the code. Sorry about that, I was doing something else at the same time and messed things up.

That sort of flipping usually breaks due to system.dataset.toDataSet() using the first output row to configure column data types. You generally need to use DatasetBuilder and explicitly set all of the column types to java.lang.Object.

Right, I forgot about types.
Also never used a dataset builder.

Could also not use a dataset at all. But I don't remember the exact structure of a table using arrays...
Is it an array of objects, with each of them containing a key/value pair for each column ?
If so, something like this:

def transform(self, value, quality, timestamp):
	columns = [value.getColumnAsList(col_index) for col_index in xrange(value.columnCount)]
	return [
		{
			'name': col[0],
			'value': col[1]
		} for col in columns
	]

Okay, you're talking over my head here, so I have no idea what to do.

That code snippet gives a scripteval error for line 4, IndexError: index out of range: 1

@pturmel where can I find more info on the dataset builder?

https://forum.inductiveautomation.com/search?q=DatasetBuilder%20order%3Alatest

Especially note this one:

Thank you.

So how and where do I use it?

Summary from the second link in the search:

def someFunction():
	dsb = DatasetBuilder.newBuilder().colNames(...).colTypes(...)
	for x in something:
		dsb.addRow(...)
	return dsb.build()

Wherever your data will confuse system.dataset.toDataSet(), due to nulls in the first row, or changing datatypes throughout the rows.

Thank you all for your help, but this just isn't clicking for me and I need to move on to a different method.

We could also explain. This is basic python, and you'll likely need to use some if you're going to use ignition. Might as well start now.
But the good news is that python is an easy language, and getting started doesn't take long.

First, I'm sorry about this bit of code... It's wrong. I shouldn't have posted it without trying it first, especially when doing something else at the same time.
It errors out because it's trying to extract the names AND the values from the values returned by the query... But the column names are not in the values.
So, this code tried to reference the second row of the table, which only has 1. That's why it raises an IndexError.

Now, this should work (and I tested it this time !):

	values = [value.getValueAt(0, col_index) for col_index in xrange(value.columnCount)]
	names = value.columnNames
	return [
		{
			'name': name,
			'value': val
		} for name, val in zip(names, values)
	]

So, how does it work ?
First, we extract the values from the first row.
There's a built-in function for extracting a value from a dataset, that looks like this:
ds.getValueAt(row_index, column_index)
Where ds is the name of the dataset. row_index and column_index start from 0 for the first one.
To get all the values in one row, we need to loop through it:

for col_index in xrange(ds.columnCount):
    ds.getValueAt(0, col_index)

We generate the column indices with xrange(n), which produces all number between 0 and n (n not included). ds.columnCount returns the number of columns in the dataset. So xrange(ds.columnCount) will produce a number for each column, that we can use as index.
Then we use ds.getValueAt(0, col_index) to get the values in the first row.
That loop will actually do nothing, as we're not storing the values anywhere. To store them, we could create a list, and append each value to that list. Or, use a list comprehension, which is pretty much syntactic sugar offered by python to make simple loops:

values = [value.getValueAt(0, col_index) for col_index in xrange(value.columnCount)]

The things used there are the same as in the loop, except the for part is at the end.
This will produce a list of all the values, that we store in a variable named values

Next, we need the column names. Luckily for us, datasets have a built-in method that does just that:
ds.getColumnNames(). We can also use ds.columnNames, which does exactly the same thing.

names = ds.columnNames

Now we have the columns names, and the values of the first row. All that's left to do is to put them together to build an array of objects. Each object will contain key/value pairs, where the key will be the column name and the value will be... the value.
Be careful here, as the column name is NOT the column of the dataset from the query, but the column name of the new table we're building. It might be confusing.
Let's say the query returns this:

| foo | bar |
| 42  | 21  |

And we want a table that looks like this:

| name | value |
| foo  | 42    |
| bar  | 21    |

The array would be

[
  {
    'name': 'foo',
    'value': 42
  },
  {
    'name': 'bar',
    'value': 21
  }
]

Each object (called dictionaries in python, surrounded by curly braces {}) will be a row in the new table, and each key/value pair in them is a column.

To build that list dynamically, we can use another loop (or list comprehension).

names = ['foo', 'bar']
values = [42, 21]
table = [
    {
        'name': name,
        'value': value
    } for name, value in zip(names, values)
]

zip(names, values) will produce pairs, composed of one element from names and one element from values. Here, it will generate [('foo', 42), ('bar', 21)]
We get one pair at each iteration of the loop, which we can use to build the object.

Putting everything together, for a script transform, where the dataset returned by the query is named value:

values = [value.getValueAt(0, col_index) for col_index in xrange(value.columnCount)]
names = value.columnNames
return [
	{
		'name': name,
		'value': val
	} for name, val in zip(names, values)
]

I hope this is clear enough, if you need a more detailed explanation about something in particular, feel free to ask. Or to google it, python is very well documented on the internet.

Notes:

  • You can change your query's return format to json, and use this script instead:
	return [
		{
			'name': name,
			'value': val
		} for name, val in value[0].items()
	]

The json return will be a list with only one element, a dictionary containing a key/value pair for each column. We use value[0] to get this single element, then call its .items() method to generate a list of of the key/value pairs inside it. The rest of the code is similar to the first solution.

  • I over simplified a few things as to not make things too confusing for now.
  • This code assumes the query ALWAYS return 1 row. Not 0, not 2.
    If it returns an empty dataset (or empty list in the case of json), it will fail. If that's a possibility, you first need to ensure the returned data is not empty.
    This is easily done with
if value.rowCount > 0:
    do_your_thing

in the case of the dataset, or

if value:
    do_your_thing

in the case of json.

3 Likes