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