QGIS API Documentation  3.2.0-Bonn (bc43194)
qgssearchquerybuilder.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgssearchquerybuilder.cpp - Query builder for search strings
3  ----------------------
4  begin : March 2006
5  copyright : (C) 2006 by Martin Dobias
6  email : wonder.sk at gmail dot com
7  ***************************************************************************
8  * *
9  * This program is free software; you can redistribute it and/or modify *
10  * it under the terms of the GNU General Public License as published by *
11  * the Free Software Foundation; either version 2 of the License, or *
12  * (at your option) any later version. *
13  * *
14  ***************************************************************************/
15 
16 #include <QDomDocument>
17 #include <QDomElement>
18 #include <QFileDialog>
19 #include <QFileInfo>
20 #include <QInputDialog>
21 #include <QListView>
22 #include <QMessageBox>
23 #include <QStandardItem>
24 #include <QTextStream>
25 
26 #include "qgssettings.h"
27 #include "qgsfeature.h"
28 #include "qgsfeatureiterator.h"
29 #include "qgsfields.h"
30 #include "qgssearchquerybuilder.h"
31 #include "qgsexpression.h"
32 #include "qgsvectorlayer.h"
33 #include "qgslogger.h"
34 #include "qgshelp.h"
35 
37  QWidget *parent, Qt::WindowFlags fl )
38  : QDialog( parent, fl )
39  , mLayer( layer )
40 {
41  setupUi( this );
42  connect( btnEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnEqual_clicked );
43  connect( btnLessThan, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLessThan_clicked );
44  connect( btnGreaterThan, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGreaterThan_clicked );
45  connect( btnLike, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLike_clicked );
46  connect( btnILike, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnILike_clicked );
47  connect( btnPct, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnPct_clicked );
48  connect( btnIn, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnIn_clicked );
49  connect( btnNotIn, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNotIn_clicked );
50  connect( lstFields, &QListView::doubleClicked, this, &QgsSearchQueryBuilder::lstFields_doubleClicked );
51  connect( lstValues, &QListView::doubleClicked, this, &QgsSearchQueryBuilder::lstValues_doubleClicked );
52  connect( btnLessEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLessEqual_clicked );
53  connect( btnGreaterEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGreaterEqual_clicked );
54  connect( btnNotEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNotEqual_clicked );
55  connect( btnAnd, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnAnd_clicked );
56  connect( btnNot, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNot_clicked );
57  connect( btnOr, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnOr_clicked );
58  connect( btnGetAllValues, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGetAllValues_clicked );
59  connect( btnSampleValues, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnSampleValues_clicked );
60  setupListViews();
61  connect( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsSearchQueryBuilder::showHelp );
62 
63  setWindowTitle( tr( "Search Query Builder" ) );
64 
65  QPushButton *pbn = new QPushButton( tr( "&Test" ) );
66  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
67  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::btnTest_clicked );
68 
69  pbn = new QPushButton( tr( "&Clear" ) );
70  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
71  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::btnClear_clicked );
72 
73  pbn = new QPushButton( tr( "&Save…" ) );
74  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
75  pbn->setToolTip( tr( "Save query to an xml file" ) );
76  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::saveQuery );
77 
78  pbn = new QPushButton( tr( "&Load…" ) );
79  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
80  pbn->setToolTip( tr( "Load query from xml file" ) );
81  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::loadQuery );
82 
83  if ( layer )
84  lblDataUri->setText( layer->name() );
85  populateFields();
86 }
87 
88 void QgsSearchQueryBuilder::populateFields()
89 {
90  if ( !mLayer )
91  return;
92 
93  const QgsFields &fields = mLayer->fields();
94  for ( int idx = 0; idx < fields.count(); ++idx )
95  {
96  QString fieldName = fields.at( idx ).name();
97  mFieldMap[fieldName] = idx;
98  QStandardItem *myItem = new QStandardItem( fieldName );
99  myItem->setEditable( false );
100  mModelFields->insertRow( mModelFields->rowCount(), myItem );
101  }
102 }
103 
104 void QgsSearchQueryBuilder::setupListViews()
105 {
106  //Models
107  mModelFields = new QStandardItemModel();
108  mModelValues = new QStandardItemModel();
109  lstFields->setModel( mModelFields );
110  lstValues->setModel( mModelValues );
111  // Modes
112  lstFields->setViewMode( QListView::ListMode );
113  lstValues->setViewMode( QListView::ListMode );
114  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
115  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
116  // Performance tip since Qt 4.1
117  lstFields->setUniformItemSizes( true );
118  lstValues->setUniformItemSizes( true );
119 }
120 
121 void QgsSearchQueryBuilder::getFieldValues( int limit )
122 {
123  if ( !mLayer )
124  {
125  return;
126  }
127  // clear the values list
128  mModelValues->clear();
129 
130  // determine the field type
131  QString fieldName = mModelFields->data( lstFields->currentIndex() ).toString();
132  int fieldIndex = mFieldMap[fieldName];
133  QgsField field = mLayer->fields().at( fieldIndex );//provider->fields().at( fieldIndex );
134  bool numeric = ( field.type() == QVariant::Int || field.type() == QVariant::Double );
135 
136  QgsFeature feat;
137  QString value;
138 
139  QgsAttributeList attrs;
140  attrs.append( fieldIndex );
141 
142  QgsFeatureIterator fit = mLayer->getFeatures( QgsFeatureRequest().setFlags( QgsFeatureRequest::NoGeometry ).setSubsetOfAttributes( attrs ) );
143 
144  lstValues->setCursor( Qt::WaitCursor );
145  // Block for better performance
146  mModelValues->blockSignals( true );
147  lstValues->setUpdatesEnabled( false );
148 
149  // MH: keep already inserted values in a set. Querying is much faster compared to QStandardItemModel::findItems
150  QSet<QString> insertedValues;
151 
152  while ( fit.nextFeature( feat ) &&
153  ( limit == 0 || mModelValues->rowCount() != limit ) )
154  {
155  value = feat.attribute( fieldIndex ).toString();
156 
157  if ( !numeric )
158  {
159  // put string in single quotes and escape single quotes in the string
160  value = '\'' + value.replace( '\'', QLatin1String( "''" ) ) + '\'';
161  }
162 
163  // add item only if it's not there already
164  if ( !insertedValues.contains( value ) )
165  {
166  QStandardItem *myItem = new QStandardItem( value );
167  myItem->setEditable( false );
168  mModelValues->insertRow( mModelValues->rowCount(), myItem );
169  insertedValues.insert( value );
170  }
171  }
172  // Unblock for normal use
173  mModelValues->blockSignals( false );
174  lstValues->setUpdatesEnabled( true );
175  // TODO: already sorted, signal emit to refresh model
176  mModelValues->sort( 0 );
177  lstValues->setCursor( Qt::ArrowCursor );
178 }
179 
180 void QgsSearchQueryBuilder::btnSampleValues_clicked()
181 {
182  getFieldValues( 25 );
183 }
184 
185 void QgsSearchQueryBuilder::btnGetAllValues_clicked()
186 {
187  getFieldValues( 0 );
188 }
189 
190 void QgsSearchQueryBuilder::btnTest_clicked()
191 {
192  long count = countRecords( txtSQL->text() );
193 
194  // error?
195  if ( count == -1 )
196  return;
197 
198  QMessageBox::information( this, tr( "Test Query" ), tr( "Found %n matching feature(s).", "test result", count ) );
199 }
200 
201 // This method tests the number of records that would be returned
202 long QgsSearchQueryBuilder::countRecords( const QString &searchString )
203 {
204  QgsExpression search( searchString );
205  if ( search.hasParserError() )
206  {
207  QMessageBox::critical( this, tr( "Query Result" ), search.parserErrorString() );
208  return -1;
209  }
210 
211  if ( !mLayer )
212  return -1;
213 
214  bool fetchGeom = search.needsGeometry();
215 
216  int count = 0;
217  QgsFeature feat;
218 
220 
221  if ( !search.prepare( &context ) )
222  {
223  QMessageBox::critical( this, tr( "Query Result" ), search.evalErrorString() );
224  return -1;
225  }
226 
227  QApplication::setOverrideCursor( Qt::WaitCursor );
228 
230 
231  while ( fit.nextFeature( feat ) )
232  {
233  context.setFeature( feat );
234  QVariant value = search.evaluate( &context );
235  if ( value.toInt() != 0 )
236  {
237  count++;
238  }
239 
240  // check if there were errors during evaluating
241  if ( search.hasEvalError() )
242  break;
243  }
244 
245  QApplication::restoreOverrideCursor();
246 
247  if ( search.hasEvalError() )
248  {
249  QMessageBox::critical( this, tr( "Query Result" ), search.evalErrorString() );
250  return -1;
251  }
252 
253  return count;
254 }
255 
256 
257 void QgsSearchQueryBuilder::btnOk_clicked()
258 {
259  // if user hits OK and there is no query, skip the validation
260  if ( txtSQL->text().trimmed().length() > 0 )
261  {
262  accept();
263  return;
264  }
265 
266  // test the query to see if it will result in a valid layer
267  long numRecs = countRecords( txtSQL->text() );
268  if ( numRecs == -1 )
269  {
270  // error shown in countRecords
271  }
272  else if ( numRecs == 0 )
273  {
274  QMessageBox::warning( this, tr( "Query Result" ), tr( "The query you specified results in zero records being returned." ) );
275  }
276  else
277  {
278  accept();
279  }
280 
281 }
282 
283 void QgsSearchQueryBuilder::btnEqual_clicked()
284 {
285  txtSQL->insertText( QStringLiteral( " = " ) );
286 }
287 
288 void QgsSearchQueryBuilder::btnLessThan_clicked()
289 {
290  txtSQL->insertText( QStringLiteral( " < " ) );
291 }
292 
293 void QgsSearchQueryBuilder::btnGreaterThan_clicked()
294 {
295  txtSQL->insertText( QStringLiteral( " > " ) );
296 }
297 
298 void QgsSearchQueryBuilder::btnPct_clicked()
299 {
300  txtSQL->insertText( QStringLiteral( "%" ) );
301 }
302 
303 void QgsSearchQueryBuilder::btnIn_clicked()
304 {
305  txtSQL->insertText( QStringLiteral( " IN " ) );
306 }
307 
308 void QgsSearchQueryBuilder::btnNotIn_clicked()
309 {
310  txtSQL->insertText( QStringLiteral( " NOT IN " ) );
311 }
312 
313 void QgsSearchQueryBuilder::btnLike_clicked()
314 {
315  txtSQL->insertText( QStringLiteral( " LIKE " ) );
316 }
317 
319 {
320  return txtSQL->text();
321 }
322 
324 {
325  txtSQL->setText( searchString );
326 }
327 
328 void QgsSearchQueryBuilder::lstFields_doubleClicked( const QModelIndex &index )
329 {
330  txtSQL->insertText( QgsExpression::quotedColumnRef( mModelFields->data( index ).toString() ) );
331 }
332 
333 void QgsSearchQueryBuilder::lstValues_doubleClicked( const QModelIndex &index )
334 {
335  txtSQL->insertText( mModelValues->data( index ).toString() );
336 }
337 
338 void QgsSearchQueryBuilder::btnLessEqual_clicked()
339 {
340  txtSQL->insertText( QStringLiteral( " <= " ) );
341 }
342 
343 void QgsSearchQueryBuilder::btnGreaterEqual_clicked()
344 {
345  txtSQL->insertText( QStringLiteral( " >= " ) );
346 }
347 
348 void QgsSearchQueryBuilder::btnNotEqual_clicked()
349 {
350  txtSQL->insertText( QStringLiteral( " != " ) );
351 }
352 
353 void QgsSearchQueryBuilder::btnAnd_clicked()
354 {
355  txtSQL->insertText( QStringLiteral( " AND " ) );
356 }
357 
358 void QgsSearchQueryBuilder::btnNot_clicked()
359 {
360  txtSQL->insertText( QStringLiteral( " NOT " ) );
361 }
362 
363 void QgsSearchQueryBuilder::btnOr_clicked()
364 {
365  txtSQL->insertText( QStringLiteral( " OR " ) );
366 }
367 
368 void QgsSearchQueryBuilder::btnClear_clicked()
369 {
370  txtSQL->clear();
371 }
372 
373 void QgsSearchQueryBuilder::btnILike_clicked()
374 {
375  txtSQL->insertText( QStringLiteral( " ILIKE " ) );
376 }
377 
379 {
380  QgsSettings s;
381  QString lastQueryFileDir = s.value( QStringLiteral( "/UI/lastQueryFileDir" ), QDir::homePath() ).toString();
382  //save as qqt (QGIS query file)
383  QString saveFileName = QFileDialog::getSaveFileName( nullptr, tr( "Save Query to File" ), lastQueryFileDir, QStringLiteral( "*.qqf" ) );
384  if ( saveFileName.isNull() )
385  {
386  return;
387  }
388 
389  if ( !saveFileName.endsWith( QLatin1String( ".qqf" ), Qt::CaseInsensitive ) )
390  {
391  saveFileName += QLatin1String( ".qqf" );
392  }
393 
394  QFile saveFile( saveFileName );
395  if ( !saveFile.open( QIODevice::WriteOnly | QIODevice::Truncate ) )
396  {
397  QMessageBox::critical( nullptr, tr( "Save Query to File" ), tr( "Could not open file for writing." ) );
398  return;
399  }
400 
401  QDomDocument xmlDoc;
402  QDomElement queryElem = xmlDoc.createElement( QStringLiteral( "Query" ) );
403  QDomText queryTextNode = xmlDoc.createTextNode( txtSQL->text() );
404  queryElem.appendChild( queryTextNode );
405  xmlDoc.appendChild( queryElem );
406 
407  QTextStream fileStream( &saveFile );
408  xmlDoc.save( fileStream, 2 );
409 
410  QFileInfo fi( saveFile );
411  s.setValue( QStringLiteral( "/UI/lastQueryFileDir" ), fi.absolutePath() );
412 }
413 
415 {
416  QgsSettings s;
417  QString lastQueryFileDir = s.value( QStringLiteral( "/UI/lastQueryFileDir" ), QDir::homePath() ).toString();
418 
419  QString queryFileName = QFileDialog::getOpenFileName( nullptr, tr( "Load Query from File" ), lastQueryFileDir, tr( "Query files" ) + " (*.qqf);;" + tr( "All files" ) + " (*)" );
420  if ( queryFileName.isNull() )
421  {
422  return;
423  }
424 
425  QFile queryFile( queryFileName );
426  if ( !queryFile.open( QIODevice::ReadOnly ) )
427  {
428  QMessageBox::critical( nullptr, tr( "Load Query from File" ), tr( "Could not open file for reading." ) );
429  return;
430  }
431  QDomDocument queryDoc;
432  if ( !queryDoc.setContent( &queryFile ) )
433  {
434  QMessageBox::critical( nullptr, tr( "Load Query from File" ), tr( "File is not a valid xml document." ) );
435  return;
436  }
437 
438  QDomElement queryElem = queryDoc.firstChildElement( QStringLiteral( "Query" ) );
439  if ( queryElem.isNull() )
440  {
441  QMessageBox::critical( nullptr, tr( "Load Query from File" ), tr( "File is not a valid query document." ) );
442  return;
443  }
444 
445  QString query = queryElem.text();
446 
447  //todo: test if all the attributes are valid
448  QgsExpression search( query );
449  if ( search.hasParserError() )
450  {
451  QMessageBox::critical( this, tr( "Query Result" ), search.parserErrorString() );
452  return;
453  }
454 
455  QString newQueryText = query;
456 
457 #if 0
458  // TODO: implement with visitor pattern in QgsExpression
459 
460  QStringList attributes = searchTree->referencedColumns();
461  QMap< QString, QString> attributesToReplace;
462  QStringList existingAttributes;
463 
464  //get all existing fields
465  QMap<QString, int>::const_iterator fieldIt = mFieldMap.constBegin();
466  for ( ; fieldIt != mFieldMap.constEnd(); ++fieldIt )
467  {
468  existingAttributes.push_back( fieldIt.key() );
469  }
470 
471  //if a field does not exist, ask what field should be used instead
472  QStringList::const_iterator attIt = attributes.constBegin();
473  for ( ; attIt != attributes.constEnd(); ++attIt )
474  {
475  //test if attribute is there
476  if ( !mFieldMap.contains( attIt ) )
477  {
478  bool ok;
479  QString replaceAttribute = QInputDialog::getItem( 0, tr( "Select Attribute" ), tr( "There is no attribute '%1' in the current vector layer. Please select an existing attribute." ).arg( *attIt ),
480  existingAttributes, 0, false, &ok );
481  if ( !ok || replaceAttribute.isEmpty() )
482  {
483  return;
484  }
485  attributesToReplace.insert( *attIt, replaceAttribute );
486  }
487  }
488 
489  //Now replace all the string in the query
490  QList<QgsSearchTreeNode *> columnRefList = searchTree->columnRefNodes();
491  QList<QgsSearchTreeNode *>::iterator columnIt = columnRefList.begin();
492  for ( ; columnIt != columnRefList.end(); ++columnIt )
493  {
494  QMap< QString, QString>::const_iterator replaceIt = attributesToReplace.find( ( *columnIt )->columnRef() );
495  if ( replaceIt != attributesToReplace.constEnd() )
496  {
497  ( *columnIt )->setColumnRef( replaceIt.value() );
498  }
499  }
500 
501  if ( attributesToReplace.size() > 0 )
502  {
503  newQueryText = query;
504  }
505 #endif
506 
507  txtSQL->clear();
508  txtSQL->insertText( newQueryText );
509 }
510 
511 void QgsSearchQueryBuilder::showHelp()
512 {
513  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
514 }
Class for parsing and evaluation of expressions (formerly called "search strings").
bool hasParserError() const
Returns true if an error occurred when parsing the input expression.
Wrapper for iterator of features from vector data provider or vector layer.
static QString quotedColumnRef(QString name)
Returns a quoted column reference (in double quotes)
QString name
Definition: qgsfield.h:57
This class is a composition of two QSettings instances:
Definition: qgssettings.h:58
QVariant value(const QString &key, const QVariant &defaultValue=QVariant(), Section section=NoSection) const
Returns the value for setting key.
void setFeature(const QgsFeature &feature)
Convenience function for setting a feature for the context.
void setSearchString(const QString &searchString)
change search string shown in text field
QVariant evaluate()
Evaluate the feature and return the result.
QString evalErrorString() const
Returns evaluation error.
Container of fields for a vector layer.
Definition: qgsfields.h:42
bool needsGeometry() const
Returns true if the expression uses feature geometry for some computation.
The feature class encapsulates a single feature including its id, geometry and a list of field/values...
Definition: qgsfeature.h:62
int count() const
Returns number of items.
Definition: qgsfields.cpp:115
QString parserErrorString() const
Returns parser error.
QgsField at(int i) const
Gets field at particular index (must be in range 0..N-1)
Definition: qgsfields.cpp:145
QgsFields fields() const override
Returns the list of fields of this layer.
Expression contexts are used to encapsulate the parameters around which a QgsExpression should be eva...
This class wraps a request for features to a vector layer (or directly its vector data provider)...
static QList< QgsExpressionContextScope * > globalProjectLayerScopes(const QgsMapLayer *layer)
Creates a list of three scopes: global, layer&#39;s project and layer.
Encapsulate a field in an attribute table or data source.
Definition: qgsfield.h:48
QgsFeatureIterator getFeatures(const QgsFeatureRequest &request=QgsFeatureRequest()) const override
Query the layer for features specified in request.
void setValue(const QString &key, const QVariant &value, QgsSettings::Section section=QgsSettings::NoSection)
Sets the value of setting key to value.
bool prepare(const QgsExpressionContext *context)
Gets the expression ready for evaluation - find out column indexes.
QgsSearchQueryBuilder(QgsVectorLayer *layer, QWidget *parent SIP_TRANSFERTHIS=nullptr, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
Constructor - takes pointer to vector layer as a parameter.
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
Definition: qgshelp.cpp:35
QString name
Definition: qgsmaplayer.h:65
QList< int > QgsAttributeList
Definition: qgsfield.h:27
bool nextFeature(QgsFeature &f)
Geometry is not required. It may still be returned if e.g. required for a filter condition.
bool hasEvalError() const
Returns true if an error occurred when evaluating last input.
Represents a vector layer which manages a vector based data sets.
QVariant attribute(const QString &name) const
Lookup attribute value from attribute name.
Definition: qgsfeature.cpp:255
QVariant::Type type
Definition: qgsfield.h:55
QString searchString()
returns newly created search string