QGIS API Documentation  3.20.0-Odense (decaadbb31)
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 
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( mTxtSql->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 ( mTxtSql->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( mTxtSql->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  mTxtSql->insertText( QStringLiteral( " = " ) );
288 }
289 
290 void QgsSearchQueryBuilder::btnLessThan_clicked()
291 {
292  mTxtSql->insertText( QStringLiteral( " < " ) );
293 }
294 
295 void QgsSearchQueryBuilder::btnGreaterThan_clicked()
296 {
297  mTxtSql->insertText( QStringLiteral( " > " ) );
298 }
299 
300 void QgsSearchQueryBuilder::btnPct_clicked()
301 {
302  mTxtSql->insertText( QStringLiteral( "%" ) );
303 }
304 
305 void QgsSearchQueryBuilder::btnIn_clicked()
306 {
307  mTxtSql->insertText( QStringLiteral( " IN " ) );
308 }
309 
310 void QgsSearchQueryBuilder::btnNotIn_clicked()
311 {
312  mTxtSql->insertText( QStringLiteral( " NOT IN " ) );
313 }
314 
315 void QgsSearchQueryBuilder::btnLike_clicked()
316 {
317  mTxtSql->insertText( QStringLiteral( " LIKE " ) );
318 }
319 
321 {
322  return mTxtSql->text();
323 }
324 
325 void QgsSearchQueryBuilder::setSearchString( const QString &searchString )
326 {
327  mTxtSql->setText( searchString );
328 }
329 
330 void QgsSearchQueryBuilder::lstFields_doubleClicked( const QModelIndex &index )
331 {
332  mTxtSql->insertText( QgsExpression::quotedColumnRef( mModelFields->data( index ).toString() ) );
333 }
334 
335 void QgsSearchQueryBuilder::lstValues_doubleClicked( const QModelIndex &index )
336 {
337  mTxtSql->insertText( mModelValues->data( index ).toString() );
338 }
339 
340 void QgsSearchQueryBuilder::btnLessEqual_clicked()
341 {
342  mTxtSql->insertText( QStringLiteral( " <= " ) );
343 }
344 
345 void QgsSearchQueryBuilder::btnGreaterEqual_clicked()
346 {
347  mTxtSql->insertText( QStringLiteral( " >= " ) );
348 }
349 
350 void QgsSearchQueryBuilder::btnNotEqual_clicked()
351 {
352  mTxtSql->insertText( QStringLiteral( " != " ) );
353 }
354 
355 void QgsSearchQueryBuilder::btnAnd_clicked()
356 {
357  mTxtSql->insertText( QStringLiteral( " AND " ) );
358 }
359 
360 void QgsSearchQueryBuilder::btnNot_clicked()
361 {
362  mTxtSql->insertText( QStringLiteral( " NOT " ) );
363 }
364 
365 void QgsSearchQueryBuilder::btnOr_clicked()
366 {
367  mTxtSql->insertText( QStringLiteral( " OR " ) );
368 }
369 
370 void QgsSearchQueryBuilder::btnClear_clicked()
371 {
372  mTxtSql->clear();
373 }
374 
375 void QgsSearchQueryBuilder::btnILike_clicked()
376 {
377  mTxtSql->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, tr( "Query files (*.qqf *.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( mTxtSql->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 *.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  mTxtSql->clear();
510  mTxtSql->insertText( newQueryText );
511 }
512 
513 void QgsSearchQueryBuilder::showHelp()
514 {
515  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
516 }
static QList< QgsExpressionContextScope * > globalProjectLayerScopes(const QgsMapLayer *layer)
Creates a list of three scopes: global, layer's project and layer.
Expression contexts are used to encapsulate the parameters around which a QgsExpression should be eva...
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.
QString parserErrorString() const
Returns parser error.
static QString quotedColumnRef(QString name)
Returns a quoted column reference (in double quotes)
Wrapper for iterator of features from vector data provider or vector layer.
bool nextFeature(QgsFeature &f)
This class wraps a request for features to a vector layer (or directly its vector data provider).
QgsFeatureRequest & setFlags(QgsFeatureRequest::Flags flags)
Sets flags that affect how features will be fetched.
QgsFeatureRequest & setSubsetOfAttributes(const QgsAttributeList &attrs)
Set a subset of attributes that will be fetched.
@ NoGeometry
Geometry is not required. It may still be returned if e.g. required for a filter condition.
The feature class encapsulates a single feature including its unique ID, geometry and a list of field...
Definition: qgsfeature.h:56
QVariant attribute(const QString &name) const
Lookup attribute value by attribute name.
Definition: qgsfeature.cpp:302
Encapsulate a field in an attribute table or data source.
Definition: qgsfield.h:51
QString name
Definition: qgsfield.h:60
QVariant::Type type
Definition: qgsfield.h:58
Container of fields for a vector layer.
Definition: qgsfields.h:45
int count() const
Returns number of items.
Definition: qgsfields.cpp:133
QgsField at(int i) const
Returns the field at particular index (must be in range 0..N-1).
Definition: qgsfields.cpp:163
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
Definition: qgshelp.cpp:36
QString name
Definition: qgsmaplayer.h:73
QgsSearchQueryBuilder(QgsVectorLayer *layer, QWidget *parent=nullptr, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
Constructor - takes pointer to vector layer as a parameter.
void setSearchString(const QString &searchString)
change search string shown in text field
QString searchString()
returns newly created search string
Represents a vector layer which manages a vector based data sets.
QgsFeatureIterator getFeatures(const QgsFeatureRequest &request=QgsFeatureRequest()) const FINAL
Queries the layer for features specified in request.
QgsFields fields() const FINAL
Returns the list of fields of this layer.
QList< int > QgsAttributeList
Definition: qgsfield.h:26
const QgsField & field
Definition: qgsfield.h:463